Run Time Error 1004: Macros Don't Work After Protecting Sheet

2
votes

I created a worksheet for my team and other agents in my field to help track sales and sales projections for each month of the year. However, everytime I set a password and save the file I get an error message stating:

Run-time error 1004 Application defined or object defined error

My code is below. Please help. I don't want to share this file unprotected. How can I protect each worksheet and still have Macros enabled without someone having to put in a code or being allowed to unprotect then protect, which would give them access to my code. Here is my code:

Public Sub ResetValues_Click()

Sheets("January").Range("D3:D3").Value = ""
Sheets("January").Range("D21:D21").Value = ""
Sheets("January").Range("H21:H21").Value = ""
Sheets("January").Range("L21:L21").Value = ""
Sheets("January").Range("P21:P21").Value = ""
Sheets("January").Range("T21:T21").Value = ""
Sheets("January").Range("X21:X21").Value = ""
Sheets("January").Range("D5:D5").Value = ""
Sheets("January").Range("G3:G3").Value = ""
Sheets("January").Range("G4:G4").Value = ""
Sheets("January").Range("C14:C18").Value = ""
Sheets("January").Range("G14:G18").Value = ""
Sheets("January").Range("D14:D18").Value = ""
Sheets("January").Range("E14:E18").Value = ""
Sheets("January").Range("G18:AP18").Value = ""
Sheets("January").Range("G17:AP17").Value = ""
Sheets("January").Range("G16:AP16").Value = ""
Sheets("January").Range("G15:AP15").Value = ""
Sheets("January").Range("G14:AP14").Value = ""
Sheets("January").Range("C27:C31").Value = ""
Sheets("January").Range("D27:D31").Value = ""
Sheets("January").Range("E27:E31").Value = ""
Sheets("January").Range("G27:G31").Value = ""
Sheets("January").Range("D34:D34").Value = ""
Sheets("January").Range("H34:H34").Value = ""
Sheets("January").Range("L34:L34").Value = ""
Sheets("January").Range("P34:P34").Value = ""
Sheets("January").Range("T34:T34").Value = ""
Sheets("January").Range("X34:X34").Value = ""
Sheets("January").Range("G27:AP27").Value = ""
Sheets("January").Range("G28:AP28").Value = ""
Sheets("January").Range("G29:AP29").Value = ""
Sheets("January").Range("G30:AP30").Value = ""
Sheets("January").Range("G31:AP31").Value = ""
Sheets("January").Range("C40:C44").Value = ""
Sheets("January").Range("D40:D44").Value = ""
Sheets("January").Range("E40:E44").Value = ""
Sheets("January").Range("D47:D47").Value = ""
Sheets("January").Range("G40:G44").Value = ""
Sheets("January").Range("G40:AP40").Value = ""
Sheets("January").Range("G41:AP41").Value = ""
Sheets("January").Range("G42:AP42").Value = ""
Sheets("January").Range("G43:AP43").Value = ""
Sheets("January").Range("G44:AP44").Value = ""
Sheets("January").Range("H47:H47").Value = ""
Sheets("January").Range("L47:L47").Value = ""
Sheets("January").Range("P47:P47").Value = ""
Sheets("January").Range("T47:T47").Value = ""
Sheets("January").Range("X47:X47").Value = ""
Sheets("January").Range("C53:C57").Value = ""
Sheets("January").Range("D53:D57").Value = ""
Sheets("January").Range("E53:E57").Value = ""
Sheets("January").Range("G53:G47").Value = ""
Sheets("January").Range("G53:AP53").Value = ""
Sheets("January").Range("G54:AP54").Value = ""
Sheets("January").Range("G55:AP55").Value = ""
Sheets("January").Range("G56:AP56").Value = ""
Sheets("January").Range("G57:AP57").Value = ""
Sheets("January").Range("D60:D60").Value = ""
Sheets("January").Range("H60:H60").Value = ""
Sheets("January").Range("L60:L60").Value = ""
Sheets("January").Range("P60:P60").Value = ""
Sheets("January").Range("T60:T60").Value = ""
Sheets("January").Range("X60:X60").Value = ""
Sheets("January").Range("C66:C70").Value = ""
Sheets("January").Range("D66:D70").Value = ""
Sheets("January").Range("E66:E70").Value = ""
Sheets("January").Range("G66:G70").Value = ""
Sheets("January").Range("D73:D73").Value = ""
Sheets("January").Range("G66:AP66").Value = ""
Sheets("January").Range("G67:AP67").Value = ""
Sheets("January").Range("G68:AP68").Value = ""
Sheets("January").Range("G69:AP69").Value = ""
Sheets("January").Range("G70:AP70").Value = ""
Sheets("January").Range("H73:H73").Value = ""
Sheets("January").Range("L73:L73").Value = ""
Sheets("January").Range("P73:P73").Value = ""
Sheets("January").Range("T73:T73").Value = ""
Sheets("January").Range("X73:X73").Value = ""

MsgBox "Values Successfully Reset!"

End Sub
1
excelvba
When you protect the sheet, use UserInterfaceOnly. This will allow macros to run, but no editing. More can be found in the Microsoft documentation docs.microsoft.com/en-us/office/vba/api/excel.worksheet.protect – Darrell H
Suggestion: declare a Worksheet local variable and assign it to ActiveWorkbook.Worksheets("January") (for equivalency with the current code - if the sheet exists in the same workbook as your VBA project's host document, then that should be ThisWorkbook ...and then you should just have a JanuarySheet code name for that sheet and zero need to dereference anything anywhere). e.g. Dim sheet As Worksheet; Set sheet = ActiveWorkbok.Worksheets("January"); and then you can do sheet.Range(...).Value = vbNullString. Also consider Union-ing the ranges so you don't need to do it 100 times. – Mathieu Guindon
Thanks to each of you. I'll start with @DarrellH since this may be easier for me because of my skill level (newbie). How do I insert that phrase into the code? Can you please provide the format? Also, by "no editing", do you mean no editing to the code or to the document? If the code, that's perfect because I don't want anyone to be able to even access the code. I have 12 sheets, a sheet for each month, so I need to know what to enter to test that code you mentioned. – Ceo Kevin Jones
@MathieuGuindon You probably said exactly what I need. However, I have no idea how to implement any of it. I knew what I wanted to do with the sheet I created so I googled how to do it and VBA popped up so after days of wrong coding I was finally successful. However, when I saved the doc and tried using the micro it no longer worked. My skill level and terminology recognition is almost non existent. If you can guide me bro I'm willing to try your solution as well. I just need to get this done today so I can deliver as promised. Thanks. – Ceo Kevin Jones
"I don't want anyone to be able to even access the code" is doomed, you cannot prevent anyone from accessing your VBA code - password protection for your VBA project is about as effective as a screen door on a submarine: don't bother with that. If someone wants to see your code, they'll get to it no matter how many speedbumps you put, often in a matter of seconds. – Mathieu Guindon

1 Answers

0
votes

Assuming the sheet January is one of 12 sheets and that all these sheets have the exact same layout and all need a similar "clean up" macro, I'd do it as follows.

First, add a standard module to your VBA project if you haven't already. Give it a meaningful name, e.g. CleanUpMacro.

The module would look like this - it's a bit hectic, because there doesn't seem to be any clear pattern in what's being cleared. Also, your code is clearing several cells multiple times; several ranges can be streamlined.

'@Folder("Macros")
Option Explicit

Public Sub CleanUpFields(ByVal monthSheet As Worksheet)
    With monthSheet
        Dim toClear As Range
        Set toClear = .Range("D3,D5,G3,G4,D21,H21,L21,P21,T21,X21")
        Set toClear = Union(toClear, .Range("C14:E18,G14:AP18,C27:E31,G27:G31"))
        Set toClear = Union(toClear, .Range("D34,H34,L34,P34,T34,X34"))
        '...
        toClear.ClearContents

        MsgBox "'" & .Name & "' sheet cleared!"
    End With
End Sub

Note that the , comma is union-ing the ranges; the reason you would want multiple Union is to keep the list somewhat organized, and the address strings under 255 characters.

If the ranges correspond to specific sections, name them. Then .Range("theGivenName") will refer to these named ranges, and I believe they can be non-contiguous, so it's possible all you need is monthSheet.Range("AllEditableFields").ClearContents, if each month sheet defines a sheet-scoped AllEditableFields named range - the advantage of this, is that it abstracts away the actual cell addresses, which means your code will not break if someone inserts a row or column somewhere, for whatever reason. It's also much easier to maintain a bunch of named ranges than a 200-liner VBA macro that refers to a bunch of individual specific cells.

Now you'll want 12 macros that invoke this procedure, each passing a different worksheet.

So the January sheet's code-behind might look like this:

'@Folder("MonthlyWorkheets")
Option Explicit

'@Ignore ProcedureNotUsed
Public Sub CleanUpFields()
    CleanUpMacro.CleanUpFields Me
End Sub

And the February sheet's code-behind might look like this:

'@Folder("MonthlyWorkheets")
Option Explicit

'@Ignore ProcedureNotUsed
Public Sub CleanUpFields()
    CleanUpMacro.CleanUpFields Me
End Sub

And the March sheet's code-behind might look like this:

'@Folder("MonthlyWorkheets")
Option Explicit

'@Ignore ProcedureNotUsed
Public Sub CleanUpFields()
    CleanUpMacro.CleanUpFields Me
End Sub

And ...well you get the idea :)

At that point all that's left to do is to wire up some Shape on the sheet to run the sheet's CleanUpFields macro when clicked, and we're done.

If you're using an ActiveX CommandButton, then your worksheets' code-behind might look like this instead:

'@Folder("MonthlyWorkheets")
Option Explicit

'@Ignore ProcedureNotUsed
Private Sub CleanUpButton_Click()
    CleanUpMacro.CleanUpFields Me
End Sub