Recording a password by macro

[Toraji3]

Hi there,

I wonder how to protect a workbook with a password leveraging macro. I am looking for a method to utilize Record Macro to make it happen without using codes in VBA. I’ve tried several ways but none of them worked.

1. Click Record Macro from the View menu

2. Select Store macro in as This Workbook / Personal Macro Workbook

3. Select the Review menu > click the Protect Workbook menu

4. Add Password (option) and Verify > click OK (Keep the Protect structure check box selected)

5. Moving back to the View menu and click Stop Recording

6. Moving back to the Review menu and click Unprotect Workbook
7. Input Password, then it works 

8. Click View Macros from the View menu, select the Macro name, and  run

9. Select the Review menu and click Unprotect Workbook again

10. The password window doesn’t pop up, instead step #4 pops up

The issue I’m encountering is that the password isn’t working for the second time and beyond. If there is no way to record a password by macro, but it’s possible to do by VBA, please let me know.

[#2 · Accepted solution]

@Toraji3 

No, the macro recorder does not record the password for the sake of security. If you look at the vba code the recorder creates, it  should look something like this:

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveWorkbook.Protect Structure:=True, Windows:=False
End Sub

But, you can edit the macro to include the password:

Sub Macro1()
    
    ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="ABC123"

End Sub

[#3]

Thank you for your response very much. The information you provided helps me to understand Excel macro more deeply.