Automation error when running VBA

christianbiker

Active Member
Joined
Feb 3, 2006
Messages
365
Greetings,

I have a spreadsheet that has been in use for some time and am now receiving an automation error for some reason. Here is the error:
Run-time error '-2147319767 (80028029)':
Automation error
Invalid forward reference, or reference to uncompiled type.

When I select the debug option, it takes me to this code and specifically highlights
VBA Code:
ws.Protect Password:=pwd1, DrawingObjects:=False, Contents:=True, Scenarios:=True, AllowFormattingCells:=True

VBA Code:
Sub protect_all_sheets()

Application.ScreenUpdating = False

    Dim pwd1 As String, pwd2 As String
    pwd1 = InputBox("Please Enter the password")
    If pwd1 = "" Then Exit Sub
    pwd2 = InputBox("Please re-enter the password")

    If pwd2 = "" Then Exit Sub

     'Check if both the passwords are identical
    If InStr(1, pwd2, pwd1, 0) = 0 Or _
    InStr(1, pwd1, pwd2, 0) = 0 Then
        MsgBox "You entered different passwords. No action taken"
        Exit Sub
    End If


    For Each ws In Worksheets
        ws.Protect Password:=pwd1, DrawingObjects:=False, Contents:=True, Scenarios:=True, AllowFormattingCells:=True
        
    Next

    MsgBox "All sheets Protected."

    Exit Sub

Application.ScreenUpdating = True

End Sub

All of the sheets are currently unlocked and I'm not sure why this error has all of a sudden started.

Any assistance would be greatly appreciated.

Thanks!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Try adding the following in you 'Dim' section at the top:

VBA Code:
    Dim ws As Worksheet
 
Upvote 0
Try adding the following in you 'Dim' section at the top:

VBA Code:
    Dim ws As Worksheet
Thanks johnnyL...that worked. I don't understand why it was working fine and now all of a sudden I needed to add that snippet of code but oh well. But now another issue has arisen. It is the opposite issue...now the code to unlock the worksheets doesn't work. ***EDIT*** this issue is happening to other macros in the same worksheet as well. I also noticed that Excel deleted the "DIM ws as Worksheet" part I added when saved and reopened the spreadsheet. When I run the below macro, it unlocks Jan, Feb & Mar but always gets hung up on Apr and won't anything further. I get the message "There is a problem - check your password, capslock, etc."

I know for a fact that the password on all remaining sheets are the same as Jan, Feb & Mar so I am stumped once again...

VBA Code:
Sub unprotect_all_sheets()

Application.ScreenUpdating = False

MSG1 = MsgBox("***WARNING***" & vbNewLine & vbNewLine & _
"USING THIS FUNCTION WILL UNLOCK ALL AREAS OF THE LIVE SCHEDULE. ANY CHANGES MADE MAY CAUSE PERMANENT CHANGES OR ISSUES." & vbNewLine & vbNewLine & _
"Click ""Yes"" to continue, ""NO"" to cancel.", vbYesNo, "UNLOCK LIVE SCHEDULE")

If MSG1 = vbYes Then
    GoTo 1
Else
    Exit Sub
End If


1:
On Error GoTo booboo
unpass = InputBox("password")
For Each Worksheet In ActiveWorkbook.Worksheets
Worksheet.Unprotect Password:=unpass
Next
Exit Sub
booboo: MsgBox "There is a problem - check your password, capslock, etc."

Worksheets("JAN").Activate
Range("B5").Select


Application.ScreenUpdating = True

End Sub

Any ideas?

Thanks!
 
Last edited:
Upvote 0
Try the following code. I made it to display the password that is being used when the unprotect is unsuccessful, and it also will continue with any remaining sheets.

VBA Code:
Sub unprotect_all_sheets()
'
    Application.ScreenUpdating = False
'
    MSG1 = MsgBox("***WARNING***" & vbNewLine & vbNewLine & _
    "USING THIS FUNCTION WILL UNLOCK ALL AREAS OF THE LIVE SCHEDULE. ANY CHANGES MADE MAY CAUSE PERMANENT CHANGES OR ISSUES." & vbNewLine & vbNewLine & _
    "Click ""Yes"" to continue, ""NO"" to cancel.", vbYesNo, "UNLOCK LIVE SCHEDULE")
'
    If MSG1 = vbYes Then GoTo 1
    Exit Sub
'
1:
    unpass = InputBox("password")
'
    For Each Worksheet In ActiveWorkbook.Worksheets
        On Error GoTo booboo
'
        Worksheet.Unprotect Password:=unpass
Continue:
'
    Next
'
    On Error GoTo 0
'
    Worksheets("JAN").Activate
    Range("B5").Select
'
    Application.ScreenUpdating = True
    Exit Sub
'
booboo:
    MsgBox "There is a problem - check your password, capslock, etc." & vbNewLine & vbNewLine & _
        "Use of the password -->" & unpass & " was not successful in unprotecting the sheet named " & Worksheet.Name
'
    On Error GoTo -1
    GoTo Continue
End Sub

It will not solve your problem, but it will verify that the password you entered is the password being used to try and unprotect the sheets when it is unsuccessful.
 
Upvote 0
Try the following code. I made it to display the password that is being used when the unprotect is unsuccessful, and it also will continue with any remaining sheets.

VBA Code:
Sub unprotect_all_sheets()
'
    Application.ScreenUpdating = False
'
    MSG1 = MsgBox("***WARNING***" & vbNewLine & vbNewLine & _
    "USING THIS FUNCTION WILL UNLOCK ALL AREAS OF THE LIVE SCHEDULE. ANY CHANGES MADE MAY CAUSE PERMANENT CHANGES OR ISSUES." & vbNewLine & vbNewLine & _
    "Click ""Yes"" to continue, ""NO"" to cancel.", vbYesNo, "UNLOCK LIVE SCHEDULE")
'
    If MSG1 = vbYes Then GoTo 1
    Exit Sub
'
1:
    unpass = InputBox("password")
'
    For Each Worksheet In ActiveWorkbook.Worksheets
        On Error GoTo booboo
'
        Worksheet.Unprotect Password:=unpass
Continue:
'
    Next
'
    On Error GoTo 0
'
    Worksheets("JAN").Activate
    Range("B5").Select
'
    Application.ScreenUpdating = True
    Exit Sub
'
booboo:
    MsgBox "There is a problem - check your password, capslock, etc." & vbNewLine & vbNewLine & _
        "Use of the password -->" & unpass & " was not successful in unprotecting the sheet named " & Worksheet.Name
'
    On Error GoTo -1
    GoTo Continue
End Sub

It will not solve your problem, but it will verify that the password you entered is the password being used to try and unprotect the sheets when it is unsuccessful.
Thanks for trying johnnyL...the code you added created an error. I see where you were going with that trying to identify the sheet that was unable to be unlocked. I have gone through each sheet personally and manually unlocked it then relocked it with the same password across the board for all sheets in the workbook. The passwords for all sheets match 100% and when I run the unprotect_all_sheets() sub it always gets stuck on the same sheet (APR) and creates an error, which doesn't make any sense since the passwords match. What am I missing??? And why now...this spreadsheet has worked for ages without issue.

If anyone can assist that would be greatly appreciated. Props to johnnyL for helping so far.
 
Upvote 0
What error and what line? It tested fine here for 4 sheets.
 
Upvote 0
Just tested it with 12 sheets, JAN,FEB,MAR,APR,MAY,JUN,etc. All protected with the same password and all of them got unprotected via the macro code.
 
Upvote 0
You might also want to check to see which error is occurring. ;)
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,757
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top