PDA

View Full Version : Checkboxes and Sheet Protection



kiyiya
07-19-2008, 05:44 AM
Hello all.

I have a "gazillion" checkboxes on a sheet that are used to put prices on the sheet. These are then looped through to find what to copy to another sheet. My problem (one of many at the moment) is that when I protect the worksheet, I get an error that the cell I am trying to change is protected..... Ok, protection works!

I have formuals that use the INDEX MATCH function to lookup the prices to put in the cells and so I thought the best way to handle it is unprotect the sheet and use my checkbox to do its thing and then protect the sheet again............. for some reason my code is not working.

Sub CB_1_Click()

application.ScreenUpdating = False
activesheet.unprotect
Sheet1.Range("H11").ClearContents
If Sheet1.Range("C11") = True Then
Sheet1.Range("H11") = "1"
Sheet1.Range("H11").Select
Else
If Sheet1.Range("c11") = False Then Sheet1.Range("H11") = ""
Sheet1.Range("F11").Select
End If
activesheet.protect
application.ScreenUpdating = True

End Sub

If I unprotect the sheet and then click the checkbox it works. However, with the sheet protected it doesn't work even though I tell it it to unprotect before going any further. It does protect the sheet when the code completes but it fails to unprotect the sheet again if I click the checkbox.

Suggestions?

xld
07-19-2008, 06:26 AM
I created a checkbox off the forms toolbar, put your code in a standard code module, and all worked fine.

Is this the same as you? Which is the activesheet?

kiyiya
07-19-2008, 08:28 AM
That is how I did it and it just doesn't work as expected. The active sheet is sheet1. I also tried sheets(sheet1).unprotect and it did not run as expected either. It works once if the sheet is unprotected but after the sheet is protected again it fails.

I guess I shoud mention that there is a formula in cell G11 that runs as well. It is =IF(C11=TRUE, INDEX(PriceList_Prices,MATCH(F11,PriceList,0)),"") but I would think that to be irrelevant since the checkbox sets C11 to true which should immediately fire the formula.

Am I wrong?

kiyiya
07-19-2008, 08:53 AM
I removed the formula and I am still getting the error.

kiyiya
07-19-2008, 09:11 AM
The "C" column is hidden on the sheet but making it visible had no effect on the outcome. However, when I unlocked the offending cell (C11) it ran perfectly every time. I assume this to be a workaround since the first line of code unprotects the sheet but it is doable since users will be unable to unhide anything with the sheet protected.

Anyone know why this behavior occurs? I am using Excel 2007.