Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
disable Tool menu | Excel Programming | |||
Enable and Disable Tool Bar | Excel Programming | |||
Hide/Disable Tool bar | Excel Programming | |||
Disable Editing tool bars | Excel Discussion (Misc queries) | |||
Disable Customize option next to a tool bar from VB | Excel Discussion (Misc queries) |