Selection.Interior.ColorIndex = 0 Error

[[email protected]]

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

[Jim Thomlinson]

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


"[email protected]" 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
>
>

[Norman Jones]

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



<[email protected]> wrote in message
news:[email protected]...
>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
>

[Jim Thomlinson]

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
>
>
> "[email protected]" 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
> >
> >