Changing the value of a protected cell from VBA

[lgbjr]

Hi All,

Is there a sheet protection scheme in Excel that allows me to set a cells
locked property to true, and protect the sheet, but still be able to change
the value of the cell from VBA?

As an example, I have a Combobox on a sheet. The combo box has 2 columns, 1
of which is shown in the dropdown. the second column value is displayed in a
cell using:

MySheet.Cells(2,2) = ComboBox.Column(2)

However, I don't want the user arbitrarily typing in cell B2 because this
value drives other formulas on the sheet. So, I lock the cell and protect
the sheet. But, then when I try to change the cells value from VBA (as
above), I can't.

Seems that there should be a way to do this without using unprotect /
protect in the VBA code everytime I want to change a locked cell's value. I
don't like the idea of having the sheet protection password pasted all over
the VBA code.

TIA
Lee

 

[lieven]

You will have to protect/unprotect the sheet from within vba.
I do this all the time. What i do is i put the password in a public constant

regards


"lgbjr" schreef:

 

[TC]

I don't like the idea of having the sheet protection password pasted all over the VBA code.

Then don't! Put it in a cell, or a public constant (as the other
respondent said), or any ther, suitable single place. Just as you would
with any other constant value that you had to reference multiple times.

Or, write a procedure to which you pass a reference of the cell or
range to be unlocked. Have a parameter, True to lock & False to unlock.
Then, the locking & unlocking code, and the password, are both in one
place only - that procedure.

HTH,
TC