PDA

View Full Version : Macro is not recording sheet protection password



Sarfaraz
08-23-2017, 12:13 PM
Hi,
I have recorded a macro in attached file. In this Macro I tried to password protect two sheets whenever I close the file, but it recorded everything and working fine except it didn't protect my sheet with my given password of "xyz123"
I would be grateful if somebody help me to put this command in my existing macro
Thanks

Paul_Hossler
08-23-2017, 04:27 PM
The macro recorder doesn't record things like passwords for security




Option Explicit
Sub Password_FileClose()
'
If Not Sheets("LF Light").ProtectContents Then
Sheets("LF Light").Protect Password:="xyz123", DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
:=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True
End If

If Not Sheets("LF Driver").ProtectContents Then
Sheets("LF Driver").Protect Password:="xyz123", DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
:=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True
End If
Sheets("LF Light").Select
'ActiveWorkbook.Save
End Sub



20159


I'd look at using the Workbook_BeforeClose event instead of the Auto macros:



Private Sub Workbook_BeforeClose(Cancel As Boolean)

End Sub

Sarfaraz
08-23-2017, 10:56 PM
Hi, I have tried the attached Macro but it didn't give me the screen to put password, is there a way if I write something in code which give me password entering screen
Regards

Paul_Hossler
08-24-2017, 05:48 AM
No

Make your own InputBox if you want and then pass the entered string to the macro in #2