Command button

  • Thread starter Thread starter Carl Johnson
  • Start date Start date
C

Carl Johnson

I've created a command button to clear a worksheet that works well when the
sheet is unprotected, but as soon as I protect it then try to execute the
clear command I get a runtime error "1004' and the debugger takes me to
Worksheets("Services Conducted").Range("Conducted").ClearContents
Could someone tell me why it works OK unprotected but will not when I do
protect it.
Thank you in advance.

Private Sub cmdServicesConducted_Click()
Dim response As Long
response = MsgBox("Caution: You are about to delete your Services Conducted
entries." & vbNewLine & vbNewLine & "Do you wish to continue?", 4, "Delete")
If response = vbYes Then
Worksheets("Services Conducted").Range("Conducted").ClearContents
End If
End Sub
 
Carl

If the worksheet is protected, and the cells you are
trying to clear are protected you can no clear them, even
from vba unless you unprotect the workbook... you can add
the code to unprotect the sheet:

ActiveSheet.Unprotect ("PassWord")
'Rest of the code here
ActiveSheet.Protect ("PassWord")

Replace "PassWord" with password or use like:
ActiveSheet.Unprotect if there is no password

The other thing you can do is select the range of cells
that compose "Conducted" and the go to
Format>Cells>Protection and untick Locked... this way
those cells will not be protected...

Cheers
Juan
 
Carl

I don't know much about VBA, but I have found that where you have a protected sheet, you can clear unprotected cells by filling them with blanks. For example, your worksheet is protected. Select the whole sheet, Tab to give the focus to the first unprotected cell, backspace to clear it, F2 then Ctrl-Enter to Fill the whole sheet. You get an error message saying you are trying to fill protected cells, but the unprotected cells are all cleared anyway. If you can turn that into VBA, maybe you will have a solution.

Vaughan
 
Back
Top