How to add a password to a marco?

[Scott_86_]

Hi, I have two separate macros that work correctly with buttons in the quick access toolbar to execute each independently.

They are 'Protect All Worksheets' & 'Unprotect All Worksheets.'

Can someone please help me to add a password just to the code 'Unprotect All Worksheets' so that when its button is pressed a password must be entered before executing.

Also, does anyone have a code to automatically 'Protect All Worksheets' when closing a workbook? (No password is needed here.)

For some reason I cannot attach an example file from my work computer. Thanks in advance.

Sub Unprotect_All()
Dim ws As Worksheet
On Error Resume Next
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect Password:="wts"
Next ws
Application.ScreenUpdating = True
On Error GoTo 0
End Sub

 

[Belleke]

Unprotect: (code in a normal module or under a button)

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

Protect: (code in ThisWorkbook section)

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
On Error Resume Next
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
ws.Protect Password:="wts"
Next ws
Application.ScreenUpdating = True
On Error GoTo 0
End Sub

If you close the workbook the sheets will be protected with password wts.
To unprotect the workbook, use wts as password to unprotect.
Note, in a inputbox, you cannot hide the password like ***
If you want that you have to make a small userform en set passwordChar *** in the textbox propery.

 

[Scott_86_]

Hi, thanks for your reply.

They both worked!

What is the difference between placing codes in a 'separate module' and 'ThisWokbook?'

And what's the difference between 'Sub' & 'Private Sub?'

Can there be conflictions with codes, certain uses, etc.?

 

[Belleke]

Public Sub - can be accessed from any module within the project
Private Sub - can be accessed only from within the same module
Sub - the same as "Public Sub"
"module" is considered as any Form, Class, Module...
There should be no conflicts if you not use the same command twice
for example, don't use Private Sub Workbook_BeforeClose(Cancel As Boolean) twice for different code (this gives an error) You need to blend the codes together.I hope it is clear, English is not my native language.

 

[Scott_86_]

Thank you. I have one more question and it's really the last thing I'm trying to figure out.

On opening the workbook I would like all worksheets to automatically go to zoom 80%.

Do you have any idea how to code this and where to put it?

 

[Belleke]

Both codes go into thisWorkbook section.
When you open the workbook zoom to 80

Private Sub Workbook_Open()
Dim ws As Worksheet
On Error Resume Next
Application.ScreenUpdating = False
    For Each ws In Worksheets
        ws.Select
    ActiveWindow.Zoom = 80
    Next ws
        Sheets("sheet 1").Select 'change to the sheet you want to start with
Application.ScreenUpdating = True
End Sub

When you close the workbook set it back to 100
See also that I blended the protectcode (from your first question)

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
Application.ScreenUpdating = False
    For Each ws In Worksheets
        ws.Select
        ws.Protect Password:="wts"
    ActiveWindow.Zoom = 100
    Next ws
Application.ScreenUpdating = True
On Error GoTo 0
End Sub

 

[vletm]

crossposted:
https://www.ozgrid.com/forum/forum/...cros/1209153-how-to-add-a-password-to-a-macro
https://www.mrexcel.com/forum/excel-questions/1072339-adding-password-macro.html