MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Requiring Password to Save File


Posted by Mav on February 02, 2002 2:59 AM

I have a workbook that I would like to add a macro to that would require a password in order to save changes to the file.
Can this be done? I prefer not to use protection as the workbook consists of 24 long sheets that require editing all day,every day.

Thanks in advance!

Mav


Posted by Barrie Davidson on February 02, 2002 7:09 AM

Yes, it can be done. Put this code in the workbook (change the password as required):

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim Password As String
Dim EnteredPassword As String

Password = "Barrie"
EnteredPassword = InputBox("Enter password to save changes")
If EnteredPassword <> Password Then Cancel = True

End Sub

If you need any help implementing this just let me know.

Regards,
Barrie
Barrie Davidson

Posted by Barrie Davidson on February 02, 2002 7:46 AM

An improvement to my code

This would be better (includes a message that the file was not saved if incorrect password supplied).

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim Password As String
Dim EnteredPassword As String

Password = "Barrie"
EnteredPassword = InputBox("Enter password to save changes")
If EnteredPassword <> Password Then
Cancel = True
MsgBox ("Password incorrect, file not saved")
End If

End Sub

Barrie Davidson

Posted by Mav on February 03, 2002 8:58 AM

Re: An improvement to my code

That is exactly what I needed!

Thanks alot.

Mav