LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 16
Default macro changes on protected cells

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
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
macro to unlock and lock cells in password protected sheet Chris Excel Discussion (Misc queries) 3 February 26th 10 09:06 PM
Macro to clear checkboxes and protected cells Guy[_2_] Excel Worksheet Functions 2 December 29th 08 08:54 PM
Edit text format in non-protected cells in protected worksheet Bonnie Excel Discussion (Misc queries) 2 April 19th 08 04:48 PM
macro to operate in protected cells HGood Excel Programming 2 December 1st 04 09:11 PM
Macro with protected cells. JD Excel Programming 3 July 21st 04 11:49 PM


All times are GMT +1. The time now is 07:47 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"