Help!

I've read all the comments I can find about this, and still can't fix it.

Problem:
I have a worksheet (We'll call it Sheet_A - not it's real name) where I need
to change the value of one cell that is protected. I've set the following in
my Workbook_Open:

Dim sh As Worksheet
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
sh.Protect "unlock", , , userinterfaceonly:=True
Next sh
Application.ScreenUpdating = True

And that seems to be working. It allows another macro from another sheet to
update the protected page in question (adding values to cells and hiding
columns based on those cell values). No problem.

BUT, within the Sheet_A, I have a drop down list that is a) populated by the
values in another sheet, and b) needs to change one cell in Sheet_A. The
value of the drop down list selection is placed in cell A1, unfortunately, a
cell I must have locked from user. So, the value isn't placed in the sheet in
cell A1.

Question is, can I put the value of my drop down list into the protected
cell? Here's the code that launches rom the dropdown list (except the value
of the drop down list isn't placed in the cell A1:

Sub DropDown11_Change()
'Selects Page within sheet to View
'Activate range with page number
Range("A1").Activate

'Find the first cell on row 1 with that value
Cells.Find(What:=ActiveCell, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext).Activate

'Select that cell - a merged cell
ActiveCell.Select

'Turn zoom off, activate merged cell in row 2, rezoom to
' ensure full screen veiw of that cell width
ActiveWindow.Zoom = False
ActiveCell.Offset(1, 0).Activate
ActiveWindow.Zoom = True
End Sub

I keep gettting a dialog that says:

The cell or chart you are trying to change is protected and therefore
read-only.

To modify a protected cell or chart, first remove protection using the
Unprotect Sheet command (Tools menu, Protection submenu). You may be prompted
for a password.

OK

Any suggestions? This is really frustrating me. I don't want to open the
cell up for the user to change.

Thanks --jkitzy

Hello,

I would think you would put the same kind of code in the dropdown routine
where you unprotect, change the cell and then re-protect the spreadsheet
again. I do that in one of my spreadsheets and it seems to work fine.

HTH, Dean.

"jkitzy" wrote:

Help!

I've read all the comments I can find about this, and still can't fix it.

Problem:
I have a worksheet (We'll call it Sheet_A - not it's real name) where I need
to change the value of one cell that is protected. I've set the following in
my Workbook_Open:

Dim sh As Worksheet
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
sh.Protect "unlock", , , userinterfaceonly:=True
Next sh
Application.ScreenUpdating = True

And that seems to be working. It allows another macro from another sheet to
update the protected page in question (adding values to cells and hiding
columns based on those cell values). No problem.

BUT, within the Sheet_A, I have a drop down list that is a) populated by the
values in another sheet, and b) needs to change one cell in Sheet_A. The
value of the drop down list selection is placed in cell A1, unfortunately, a
cell I must have locked from user. So, the value isn't placed in the sheet in
cell A1.

Question is, can I put the value of my drop down list into the protected
cell? Here's the code that launches rom the dropdown list (except the value
of the drop down list isn't placed in the cell A1:

Sub DropDown11_Change()
'Selects Page within sheet to View
'Activate range with page number
Range("A1").Activate

'Find the first cell on row 1 with that value
Cells.Find(What:=ActiveCell, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext).Activate

'Select that cell - a merged cell
ActiveCell.Select

'Turn zoom off, activate merged cell in row 2, rezoom to
' ensure full screen veiw of that cell width
ActiveWindow.Zoom = False
ActiveCell.Offset(1, 0).Activate
ActiveWindow.Zoom = True
End Sub

I keep gettting a dialog that says:

The cell or chart you are trying to change is protected and therefore
read-only.

To modify a protected cell or chart, first remove protection using the
Unprotect Sheet command (Tools menu, Protection submenu). You may be prompted
for a password.

OK

Any suggestions? This is really frustrating me. I don't want to open the
cell up for the user to change.

Thanks --jkitzy

I tried that. The problem seems to be that in the Drop Down List properties,
I have the selected value placed in the locked cell. I don't get the
opportunity to unlock the cell before I get the dialog box because this event
happens prior to the "change" event. Unless I'm missing something.

"Dean Hinson" wrote:

Hello,

I would think you would put the same kind of code in the dropdown routine
where you unprotect, change the cell and then re-protect the spreadsheet
again. I do that in one of my spreadsheets and it seems to work fine.

HTH, Dean.

Well, this seems to be similar to the problem I had. Like you I didn't find
the ultimate answer. So what I did was place my dropdown list and the
selected index in a hidden worksheet that was not protected. I do not know if
it will work with the very-hidden option because I have not tired it. This
might not be the solution you were looking for but it is what I implemented.

If you do find a guru with the ultimate solution, please post so that we all
can benefit.

Regards, Dean.

"jkitzy" wrote:

I tried that. The problem seems to be that in the Drop Down List properties,
I have the selected value placed in the locked cell. I don't get the
opportunity to unlock the cell before I get the dialog box because this event
happens prior to the "change" event. Unless I'm missing something.

"Dean Hinson" wrote:

Hello,

I would think you would put the same kind of code in the dropdown routine
where you unprotect, change the cell and then re-protect the spreadsheet
again. I do that in one of my spreadsheets and it seems to work fine.

HTH, Dean.

Well, since I'm on a SERIOUS time crunch on this project, I just went ahead
and unprotected the cell I needed to use. My risk is pretty small. For my
use, even if a user messes up and puts something else in the cell (which they
would have to do pretty deliberately), if they reselect from the DropDown
List, they'll be okay.

Not graceful, but, hey, not everything is!

Thanks again for your help

"Dean Hinson" wrote:

Well, this seems to be similar to the problem I had. Like you I didn't find
the ultimate answer. So what I did was place my dropdown list and the
selected index in a hidden worksheet that was not protected. I do not know if
it will work with the very-hidden option because I have not tired it. This
might not be the solution you were looking for but it is what I implemented.

If you do find a guru with the ultimate solution, please post so that we all
can benefit.

Regards, Dean.