VBA Save As with Password

[Andybuck86]

Hi,

I really need some help please!

I have the following VBA in a module in my workbook

Option Explicit
Sub Save_Password()
Dim Open_Password As String
Dim New_File_Name As String
New_File_Name = Range("G2").Value & "\" & Range("G1").Value & ".xlsm"
Open_Password = InputBox("Please enter a password to stop others from editing your timesheet. YOU MUST REMEMBER THIS PASSWORD")
ActiveWorkbook.SaveAs New_File_Name, _
WriteResPassword:=Open_Password, _
Password:="heather"

' ActiveWorkbook.SaveAs New_File_Name, _ ' creates a new file with the name in G1
' Password:=Open_Password, _ ' this is the password the user enters
' WriteResPassword:="password" ' this is the master password
End Sub

This is assigned to a button. When pressed it requests the user create a write password and saves the file based on variable cells in the workbook.

This works great HOWEVER when the user then goes back into the workbook and makes changes, the save as button will not work. When pressed the message 'Can not access.....' comes up.

I think this is potentially because the workbook is password protected??

Does anybody know of a solution?

Many Thanks in Advance


Andy

[Bob Phillips]

Save the password, and when they next access it, unprotect the filw.

[Andybuck86]

Save the password, and when they next access it, unprotect the filw.

Thanks for the response but I'm not sure how that would work?

Surely it still needs to overwrite the password protected workbook?

I was thinking that maybe something like IF FILE EXISTS THEN JUST SAVE else if not SAVE AS. Would anyone be able to help me create that code?

Thanks

[Paul_Hossler]

Maybe instead of password protecting the workbook, you could password protect the worksheet and of course the VBA project.

Store the user entered PW on a very hidden sheet, and in order to unlock the WS for data entry, they need to enter the correct matching PW.

Paul

[Sean.DiSanti]

have your button:
ActiveWorkbook.Protect(InputBox("Enter Your password","Password","Password"))
and set this into your Workbook_Open event
If ActiveWorkbook.HasPassword Then ActiveWorkbook.Unprotect (InputBox("Enter Your password", "Password", "Password"))