J
jkitzy
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'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