excel disable save but enable with password prompt?

Hi

currently i have save, save as and save on exit disabled in excel as it is an induction form with personal information so i do not want anyone to be able to save that information. However the HR department occasionally needs to amend any mistake or information on that form. I was thinking of having the save etc disabled but can be renabled with the correct password so those changes can be made?

is this at all possible?

thanks

thats really cool. seems to work. i will do some more testing

thanks for that

is there anyway to stop excel greying out the save buttons after using the code to grey them out?

sometimes when we have used the sheet with the code that disables the buttons even when that sheet isn't open the save remains disabled in excel

i then have to rename the .xlb file

here is the code i use to disable save and save as.

is there alternative code that would stop this problem happening?

Private Sub myApp_WorkbookActivate(ByVal Wb As Workbook)
If Wb Is Me Then DisableSaveOptions
End Sub

Private Sub myApp_WorkbookDeactivate(ByVal Wb As Workbook)
If Wb Is Me Then EnableSaveOptions
End Sub

Private Sub Workbook_Open()
Set myApp = Application
DisableSaveOptions
End Sub




Private Sub DisableSaveOptions()
Dim ctls As Object
Dim ctl As Object
'disable the Save button
Set ctls = Application.CommandBars.FindControls(ID:=3)
For Each ctl In ctls
ctl.Enabled = False
Next
'disable the Save and Save As... options in the Menu
Set ctls = Application.CommandBars.FindControls(ID:=748)
For Each ctl In ctls
ctl.Enabled = False
Next
'disable the Save short-cut
Application.OnKey "^s", ""
End Sub


Private Sub EnableSaveOptions()
Dim ctls As Object
Dim ctl As Object
'enable the Save button
Set ctls = Application.CommandBars.FindControls(ID:=3)
For Each ctl In ctls
ctl.Enabled = True
Next
'enable the Save and Save As... options in the Menu
Set ctls = Application.CommandBars.FindControls(ID:=748)
For Each ctl In ctls
ctl.Enabled = True
Next
'enable the Save short-cut
Application.OnKey "^s"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveWorkbook.Saved = True
End Sub

Originally Posted by royUK

You aren't using the inbuilt Sheet Activate & Deactivate events.

Also, please use Code Tags not Quote tags

What is the sheet activate and deactivate events?

thanks

I am trying to use the code RoyUK posted but the inputbox pops up twice. I need to enter the password twice in order to save or press cancel twice to cancel

Does anyone know what might cause this?

Thanks for your help!