Disable Save on Tool Bar?

[Guest]

Is it possible to disable and hide the save button from the tool bar as well
as disable Ctrl + S and Save As?

I want the command button I have built to be the only method available to
save the file.

Thanks for any suggestions.

 

[Peter T]

You could customize toolbars to remove those menus and assign shortcut
Ctrl-S to your own macro.

Another approach might be to trap the save event at application level in
your main workbook or an addin and process, eg

' in a Class1 change name later to say clsApp
Public WithEvents xlApp As Excel.Application

Private Sub xlApp_WorkbookBeforeSave(ByVal Wb As Workbook, _
ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet
On Error GoTo errH

'perhaps first some test if Wb properties are relevant

Set ws = Wb.Worksheets("Sheet1")
' will error & run to errH if no Sheet1

If ws.Range("A1") = 111 Then
'do nothing, normal save will occur
ElseIf ws.Range("A1") = 222 Then
'stop normal save
'change 222 > 333 and save
'prevent events running while changing cell
' & doing own save
Cancel = True
xlApp.EnableEvents = False
ws.Range("A1") = 333
Wb.Save
Else
Cancel = True ' stop the save
MsgBox "Sheet1:A1 wrong", , "Save cancelled"
End If

errH:
xlApp.EnableEvents = True
End Sub

' in a normal module
Dim clAppEvents As Class1

Sub auto_open()
'manually run to test

Set clAppEvents = New Class1
Set clAppEvents.xlApp = Application
End Sub

Regards,
Peter T

 

[Guest]

Just to add,

if this functionality is for only one workbook, you can just use the
workbook level beforesave event.

 

[Guest]

Could you expand further on that thought?
This is for one workbook that will be distributed to several users.

 

[Guest]

Tom,

After being pointed in this direction (Beforesave event) I found how to make
it work. Thanks! Now on to the next problem.

Tim