Coralie has attended:
Excel Advanced course
Lock/unlock sheet and workbook with a password in a Macro
Good morning,
I have an Excel Spreadhseet for which I have the following macro:
- Name: Lock_Sheet_Workbook
- Recording:
Sub lock_sheet()
'
' lock_sheet Macro
'
' Keyboard Shortcut: Ctrl+Shift+L
'
Sheets("sheet1").Select
ActiveSheet.protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("sheet2").Select
ActiveSheet.protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.protect Structure:=True, Windows:=False
End Sub
>>>> WHAT I WANT TO DO is:
Lock sheet1 with password "ABC", Lock sheet2 with password "ABC", lock workbook with password "ABC"
My Question to you: How and where can I write this in my VBA?
Many thanks,
Coralie
RE: Lock/unlock sheet and workbook with a password in a Macro
Hi Coralie,
Thank you for the forum question.
The code in your macro will protect the workbook.
The VBA code will do the job:
Sub ProtectMyworkbook()
ThisWorkbook.Protect Password:="myPassword"
End Sub
If you only want to protect a spreadsheet use the code below. You will need to change "Sheet1" to the name of the worksheet you want to protect:
Sub ProtectSheetWithPassword()
Sheets("Sheet1").Protect Password:="myPassword"
End Sub
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Lock/unlock sheet and workbook with a password in a Macro
Sorry
I only answered half of the question.
You will have to write the code in the visual basic editor.
Press ALT F11 (if this is not opening the visual basic editor press ALT FN F11. On some laptops you will need to use FN to be able to use the F keys).
In the visual basic editor click INSERT MODULE at the top.
Type the code in the module. You will need to save the file as a MACRO ENABLE WORKBOOK in the Save As dialog box. The extension for a macro enable workbook is XLSM instead of the "normal" extension XLSX.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Lock/unlock sheet and workbook with a password in a Macro
Hi, thank you, all works
Many thanks,
Coralie
RE: Lock/unlock sheet and workbook with a password in a Macro
You are welcome.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
