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

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" wrote:

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

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

Any other ideas?

Ben

"JLatham" wrote:

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" wrote:

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

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

Ben

"Ben in CA" wrote:

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

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" wrote:

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

Any other ideas?

Ben

"JLatham" wrote:

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" wrote:

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

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" wrote:

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" wrote:

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

Any other ideas?

Ben

"JLatham" wrote:

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" wrote:

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

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.

"Ben in CA" wrote:

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" wrote:

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" wrote:

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

Any other ideas?

Ben

"JLatham" wrote:

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" wrote:

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