Excel - randomly locking cells?

[Ben in CA]

Hi,

I'm having an issue with some code, that only started being a problem once I
protected the worksheet.

I have the following macro that clears some cells and checkboxes - tied to a
form button called Clear.

Sub Clear()
Application.ScreenUpdating = False
Columns("U:W").Clear ' Clear columns
Columns("U:W").NumberFormat = "$#,##0.00" ' Make Currencey format
Range("U1").Select ' Select a single cell to avoid confusion
For Each Shp In ActiveSheet.Shapes ' Clear Checkboxes
X = Shp.Type
If X = msoFormControl Then
If Shp.FormControlType = xlCheckBox Then
Shp.ControlFormat.Value = False
End If
End If
Next Shp
Application.ScreenUpdating = True
End Sub

Here's what I'm doing:

I select columns U, V, and W.

I right click, format cells, and uncheck Lock Cells on the protection tab.

I protect my worksheet.

The I can enter random values in these columns.

Then, I want to clear them - and I click clear. It clears them, and no errors.

But the next time I press clear, I get this error message:

Runtime Error '1004'
The cell or chart that you are trying to change is protected and therefore
read-only.

Any ideas why?

(And if I then unprotect the sheet, they show up as locked when I check
their properties - and yet I could change their contents when it was
protected.)

Thanks in advance,

Ben

 

[JLatham]

I don't see anything in the code that would cause this kind of activity, BUT
I don't like the name of your sub, and I suspect that neither does Excel.
'Clear' is a special operation - as you already know, having used it to clear
the contents & format of columns U:W with it. I'd start by renaming the sub
to something like
ClearColumnsUVW() and see if the problem persists.

 

[Ben in CA]

Thanks for the response. I've tried that, but unfortunately it doesn't solve
the problem.

Any other ideas?

Ben

 

[Ben in CA]

I don't know if this matters, but there's some hidden rows???

Ben

 

[JLatham]

Sorry, but I just don't see that happening from your code. However, when I
copy your code and run it on a sheet (without any shapes/controls on it) and
then protect the sheet, as I expected I get an error at the very first
executable:
Columns("U:W").Clear

Try adding these lines of code (assuming no password for the sheet). Start
by making this the first line of the macro:
ActiveSheet.Unprotect

then at the very end, just before End Sub, add
ActiveSheet.Protect

Or, if you've assigned a password, they would be something like this, with
the actual password instead of "MyPW"
ActiveSheet.Unprotect password:="MyPW"
ActiveSheet.Protect password:="MyPW"

Perhaps that will help.

 

[Ben in CA]

Excellent - that solves my problem perfectly!

Thanks so much!

ActiveSheet.Unprotect password:="MyPW"
ActiveSheet.Protect password:="MyPW"

I put in that before and after the code that was causing the problem, and it
works great.

I didn't realize that I could protect and unprotect from a macro.

Ben

 

[JLatham]

Glad that solved the problem. Trust me on this: you can do 99.99% of
anything you can do from the keyboard within code. Sometimes doing a thing
in code is the ONLY way to automate an action - such as actually
hiding/unhiding rows/columns/sheets: those things can't be done through a
formula and so must be done by hand or by code.

That's not to put down formulas or even sometimes requiring some manual
action. I've seen cases where I wrote lines and lines (and MORE lines) of
code to do something, and the same thing could be accomplished relatively
easily with a formula.