Macro changes from Excel 2010 to 2019

spectraflame

Well-known Member
Joined
Dec 18, 2002
Messages
815
I have been using the following code that someone sent me for years to help with protecting / unprotecting all sheets within a workbook. When I attempt to run this Macro in Excel 2019, it fails with an error of: "Cannot find project or library". Were there changes within Excel 2019 that are causing this?

VBA Code:
Sub protect_all_sheets()
top:
pass = InputBox("Key in Desired Password?")
repass = InputBox("Please Verify Password")
If Not (pass = repass) Then
MsgBox "Please Enter Password"
GoTo top
End If
For i = 1 To Worksheets.Count
If Worksheets(i).ProtectContents = True Then GoTo oops
Next
For Each s In ActiveWorkbook.Worksheets
s.Protect Password:=pass
Next
Exit Sub
oops: MsgBox "I think you have some sheets that are already protected. Please unprotect all sheets then running this Macro."
End Sub

Sub unprotect_all_sheets()
On Error GoTo booboo
unpass = InputBox("Key in Password")
For Each Worksheet In ActiveWorkbook.Worksheets
Worksheet.Unprotect Password:=unpass
Next
Exit Sub
booboo: MsgBox "There is s problem - check your password, capslock, etc."
End Sub
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,492
Office Version
  1. 365
Platform
  1. Windows
Did you check the VBA References for a missing library? Usually it is just a matter of selecting it.

If it is not obvious which one is missing, then do the following:
1. From the computer/system where it DOES work, go into the VB Editor, go to the Tools menu, and select References. Note all the ones that are checked.
2. From the computer/system where it doed NOT work, repeat the same steps, and make sure all the same References are selected. If they are not, find and select the missing ones.
(Note that the version number may be different, but the rest of the Reference name should be the same).
 

spectraflame

Well-known Member
Joined
Dec 18, 2002
Messages
815
The only difference is the Microsoft Excel 14.0 Object Library and the Microsoft Office 14.0 Object Library. Does not appear those are options in the 2019 version of Excel.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,492
Office Version
  1. 365
Platform
  1. Windows
The only difference is the Microsoft Excel 14.0 Object Library and the Microsoft Office 14.0 Object Library. Does not appear those are options in the 2019 version of Excel.
As I alluded to in my previous post, the versions may be different. Just select the closest matches.

On my Excel, it is:
- Microsoft Excel 16.0 Object Library
- Microsoft Office 16.0 Object Library
 

spectraflame

Well-known Member
Joined
Dec 18, 2002
Messages
815
I have those 16 libraries selected on mine as well. Wondering if the syntax for Worksheet.Unprotect Password is different.
 
Last edited:

spectraflame

Well-known Member
Joined
Dec 18, 2002
Messages
815
Found a new macro on ExcelHow that seems to work. Appears to use different syntax.

VBA Code:
Sub UnProtectAllWorksheets ()
    Dim ws As Worksheet
    Dim Pwd As String
    Pwd = InputBox("Enter your password to unprotect all worksheets", "Unprotect Worksheets")
    On Error Resume Next
    For Each ws In Worksheets
        ws.Unprotect Password:=Pwd
    Next ws
    If Err <> 0 Then
        MsgBox "You have entered an incorect password. All worksheets could not " & _
"be unprotected.", vbCritical, "Incorect Password"
    End If
    On Error GoTo 0
End Sub

Sub ProtectAllWorksheets()
    Dim ws As Worksheet
    Dim Pwd As String
    Pwd = InputBox("Enter your password to protect all worksheets", "Protect Worksheets")
    For Each ws In ActiveWorkbook.Worksheets
        ws.Protect Password:=Pwd
    Next ws
End Sub
 
Last edited by a moderator:
Solution

Watch MrExcel Video

Forum statistics

Threads
1,126,952
Messages
5,621,798
Members
415,856
Latest member
jimb2k

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
Top