VBA Debug Error & VBA Not Working When Sheet Is Locked

[VinceF]

Two problems please.
Novice user (thankful for this forum and Google)

Problem 1. When the sheet is locked the VBA code to reset the sheet doesn't clear the cells as designed. If the sheet is unlocked, it works fine.
Problem 2. When resetting the sheet with VBA I keep getting a Runtime Error 1004, no cells found. It mostly points towards this line
Sheets("main").Range("e12:g51").SpecialCells(xlConstants).ClearContents

Thank you in advance for your assistance.

VinceF
Win 10
Excel 2016

rivate Sub CommandButton1_Click()

Dim warning
warning = MsgBox(Range("A1").Value & "    WARNING !!!! WARNING !!! WARNING !!! This will reset the entire sheet.  Select OK to continue or select CANCEL to continue without resetting", vbOKCancel, "Warning")
If warning = vbCancel Then Exit Sub

Sheets("main").Range("q4").Value = "SELECT COURSE"
Sheets("main").Range("I4").Value = "SELECT TEE"
Sheets("main").Range("W4").Value = "PLAYING CONDITIONS"
Sheets("main").Range("AC4").Value = "BUNKER RELIEF"
Sheets("main").Range("B4").Value = "GAME TYPE"
Sheets("main").Range("E4").Value = "SKINS FEE"
Sheets("main").Range("B7").Value = ""
Sheets("main").Range("F4").Value = "STABLEFORD FEE"
Sheets("main").Range("AG4").Value = "POINT/PLACE"
Sheets("main").Range("D51").Value = ""
Sheets("main").Range("AE51").Value = ""
Sheets("main").Range("ag7:aj7").Value = ""
Sheets("main").Range("B12:d51").Value = ""
Sheets("main").Range("i12:r51").Value = ""
Sheets("main").Range("t12:ab51").Value = ""
Sheets("main").Range("e12:g51").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("AG12:AK51").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("I12:AE51").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("I12:r12").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("I13:r13").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("AH4").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("AI4").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("AJ4").SpecialCells(xlConstants).ClearContents



MsgBox "THE FORM HAS BEEN RESET."

End Sub

 

[Joe4]

Problem 1. When the sheet is locked the VBA code to reset the sheet doesn't clear the cells as designed. If the sheet is unlocked, it works fine.

If your sheet is protected, you will need to unprotect your sheet before performing those steps on it. What most people do is add lines to the top of your VBA that unprotect the sheet, then perform your actions, then add lines to the bottom of your code to re-protect the sheet.

See here: VBA Protect / Unprotect Worksheets - Automate Excel

Problem 2. When resetting the sheet with VBA I keep getting a Runtime Error 1004, no cells found. It mostly points towards this line
Sheets("main").Range("e12:g51").SpecialCells(xlConstants).ClearContents

That means it is not finding any constants in those cells to clear.

You can ignore that error by placing this line of code above that line:

On Error Resume Next

Then just be sure to add this line after all those ".SpecialCells(xlConstants).ClearContents" lines:

On Error GoTo 0

to turn the error messages back on.

 

[VinceF]

Joe,
Happy I'm to report that it's now working great.
Thank you very much for your assistance...!

VinceF

 

[Joe4]

You are welcome.
Glad I was able to help!