SOLVED

Recording a password by macro

%3CLINGO-SUB%20id%3D%22lingo-sub-2355619%22%20slang%3D%22en-US%22%3ERecording%20a%20password%20by%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2355619%22%20slang%3D%22en-US%22%3E%3CDIV%20class%3D%22thread-message-content-body-text%20thread-full-message%22%3E%3CP%20class%3D%22%22%3EHi%20there%2C%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EI%20wonder%20how%20to%20protect%20a%20workbook%20with%20a%20password%20leveraging%20macro.%20I%20am%20looking%20for%20a%20method%20to%20utilize%20Record%20Macro%20to%20make%20it%20happen%20without%20using%20codes%20in%20VBA.%20I%E2%80%99ve%20tried%20several%20ways%20but%20none%20of%20them%20worked.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3COL%3E%3CLI%3E%3CP%3EClick%20Record%20Macro%20from%20the%20View%20menu%3C%2FP%3E%3C%2FLI%3E%3CLI%3E%3CP%3ESelect%20Store%20macro%20in%20as%20This%20Workbook%20%2F%20Personal%20Macro%20Workbook%3C%2FP%3E%3C%2FLI%3E%3CLI%3E%3CP%3ESelect%20the%20Review%20menu%20%26gt%3B%20click%20the%20Protect%20Workbook%20menu%3C%2FP%3E%3C%2FLI%3E%3CLI%3E%3CP%3EAdd%20Password%20(option)%20and%20Verify%20%26gt%3B%20click%20OK%20(Keep%20the%20Protect%20structure%20check%20box%20selected)%3C%2FP%3E%3C%2FLI%3E%3CLI%3E%3CP%3EMoving%20back%20to%20the%20View%20menu%20and%20click%20Stop%20Recording%3C%2FP%3E%3C%2FLI%3E%3CLI%3EMoving%20back%20to%20the%26nbsp%3BReview%20menu%20and%20click%20Unprotect%20Workbook%3C%2FLI%3E%3CLI%3EInput%20Password%2C%20then%20it%20works%26nbsp%3B%3C%2FLI%3E%3CLI%3E%3CP%3EClick%20View%20Macros%20from%20the%20View%20menu%2C%20select%20the%20Macro%20name%2C%20and%26nbsp%3B%20run%3C%2FP%3E%3C%2FLI%3E%3CLI%3E%3CP%3ESelect%20the%20Review%20menu%20and%20click%20Unprotect%20Workbook%20again%3C%2FP%3E%3C%2FLI%3E%3CLI%3E%3CP%3EThe%20password%20window%20doesn%E2%80%99t%20pop%20up%2C%20instead%20step%20%234%20pops%20up%3C%2FP%3E%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20issue%20I%E2%80%99m%20encountering%20is%20that%20the%20password%20isn%E2%80%99t%20working%20for%20the%20second%20time%20and%20beyond.%20If%20there%20is%20no%20way%20to%20record%20a%20password%20by%20macro%2C%20but%20it%E2%80%99s%20possible%20to%20do%20by%20VBA%2C%20please%20let%20me%20know.%3C%2FP%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2355619%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2356635%22%20slang%3D%22en-US%22%3ERe%3A%20Recording%20a%20password%20by%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2356635%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1054096%22%20target%3D%22_blank%22%3E%40Toraji3%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENo%2C%20the%20macro%20recorder%20does%20not%20record%20the%20password%20for%20the%20sake%20of%20security.%20If%20you%20look%20at%20the%20vba%20code%20the%20recorder%20creates%2C%20it%26nbsp%3B%20should%20look%20something%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESub%20Macro1()%0A'%0A'%20Macro1%20Macro%0A'%0A%0A'%0A%20%20%20%20ActiveWorkbook.Protect%20Structure%3A%3DTrue%2C%20Windows%3A%3DFalse%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%2C%20you%20can%20edit%20the%20macro%20to%20include%20the%20password%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESub%20Macro1()%0A%20%20%20%20%0A%20%20%20%20ActiveWorkbook.Protect%20Structure%3A%3DTrue%2C%20Windows%3A%3DFalse%2C%20Password%3A%3D%22ABC123%22%0A%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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 Replies
best response confirmed by Toraji3 (New Contributor)
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

 

 

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