I have a combo box and based on the answer in the combo box, I need the
cell color to change to 'gray out' when then answer is no. It works
great without protection turned on, but once I turn on sheet
protection, it doesn't work anymore. I need the rest of the sheet
protected. I unchecked the 'locked' and 'hidden' boxes on the cell I
need to turn gray. I also have the value of the cell changing from 65
to N/A and that part works great.

I'm stumped. What do I do?

Thanks in advance,
Laura

Unprotect the sheet while the macro is running and re-protect it at the end

Sub DoWhatever()
activesheet.unprotect "MyPassword"
'Do your Stuff
activesheet.protect "MyPassword"
end sub

--
HTH...

Jim Thomlinson


" wrote:

I have a combo box and based on the answer in the combo box, I need the
cell color to change to 'gray out' when then answer is no. It works
great without protection turned on, but once I turn on sheet
protection, it doesn't work anymore. I need the rest of the sheet
protected. I unchecked the 'locked' and 'hidden' boxes on the cell I
need to turn gray. I also have the value of the cell changing from 65
to N/A and that part works great.

I'm stumped. What do I do?

Thanks in advance,
Laura

Hi Laura,

Alternatively, try setting the Protect method's UserInterfaceOnly parameter
to True which enables vba manipulation of the protected sheet.

However, this setting is not persistent and needs to be reset each time the
workbook is opened.

Perhaps, therefore, you could set protection in the Workbook_Open or
Auto_Open procedures, e.g.:

Sub Auto_Open()
With Worksheets("Your Sheet Name")
.Protect Password:="Your Password", UserInterfaceOnly:=True
End With
End Sub


---
Regards,
Norman



wrote in message
oups.com...
I have a combo box and based on the answer in the combo box, I need the
cell color to change to 'gray out' when then answer is no. It works
great without protection turned on, but once I turn on sheet
protection, it doesn't work anymore. I need the rest of the sheet
protected. I unchecked the 'locked' and 'hidden' boxes on the cell I
need to turn gray. I also have the value of the cell changing from 65
to N/A and that part works great.

I'm stumped. What do I do?

Thanks in advance,
Laura

Sorry I should also have mentioned taht depending what version of Excel you
are running you cna check the User Interface Only option. With this checked
VBA has free reign to do what it wants but the user is restricted...
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

Unprotect the sheet while the macro is running and re-protect it at the end

Sub DoWhatever()
activesheet.unprotect "MyPassword"
'Do your Stuff
activesheet.protect "MyPassword"
end sub

--
HTH...

Jim Thomlinson


" wrote:

I have a combo box and based on the answer in the combo box, I need the
cell color to change to 'gray out' when then answer is no. It works
great without protection turned on, but once I turn on sheet
protection, it doesn't work anymore. I need the rest of the sheet
protected. I unchecked the 'locked' and 'hidden' boxes on the cell I
need to turn gray. I also have the value of the cell changing from 65
to N/A and that part works great.

I'm stumped. What do I do?

Thanks in advance,
Laura