Disable Save on Tool Bar?
 

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.

Disable Save on Tool Bar?
 

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


"TimN" wrote in message
...
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.

Disable Save on Tool Bar?
 

Just to add,

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

--
Regards,
Tom Ogilvy


"Peter T" wrote:

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


"TimN" wrote in message
...
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.

Disable Save on Tool Bar?
 

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

"Tom Ogilvy" wrote:

Just to add,

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

--
Regards,
Tom Ogilvy


"Peter T" wrote:

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


"TimN" wrote in message
...
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.

Disable Save on Tool Bar?
 

Tom,

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

Tim

"Tom Ogilvy" wrote:

Just to add,

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

--
Regards,
Tom Ogilvy


"Peter T" wrote:

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


"TimN" wrote in message
...
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.