Disabling Or Removing The Save Function In A Spreadsheet
I've searched through the VBA section trying to find examples of how to do what I'm looking for and turned up empty. Perhaps I didn't use the correct search criteria, but I couldn't find an answer, so I'm asking the questions.
I'm going to be rolling out an Excel Spreadsheet as a template with an app I'm working on. I want to ensure the template is always empty when opened, so I need to force the users to use only Save As...
Needs:
Disable or Remove the Save button on the toolbar
Disable or Remove Save from the drop down menu
Nice To Have:
When an existing spreadsheet is opened, the name of the spreadsheet appears in the Spreadsheet's windows caption. Is there a way to remove that from appearing?
When Save As is selected from the drop down menu, the name of the spreadsheet appears in the file name textbox of the common dialog. Is there a way to remove that?
Re: Disabling Or Removing The Save Function In A Spreadsheet
We can do all.
First I have my CodeBank example on Creating Excel Events. This will show how to trap for the save as dialog
and bring up your own.
To disable or remove the Save menu item and toolbar button you need to use the CommandBar collection. I think its
the "Menu Bar" for the menu and I'll post back with the toolbar name.
Re: Disabling Or Removing The Save Function In A Spreadsheet
Rats! I should have looked in the CodeBank as well as doing a search.
Ok...how about the removing the name from the commondialog Save As and the spreadsheet title bar?
Re: Disabling Or Removing The Save Function In A Spreadsheet
Hack, check out these links to.
Command Bars
http://www.vbforums.com/showthread.php?t=297189
Change icon/title
http://www.vbforums.com/showthread.php?t=285959
To change the window caption you wil need to use SendMessage with FindWindow or you can test by changing
the .Name property of the workbook.
I may be off-line for a while today so sorry I cant write a nice example for you. If you dont have it by the
evening I will if you want. ;)
Got to go.
Later
Re: Disabling Or Removing The Save Function In A Spreadsheet
Thanks Rob. I'll give it a shot an let you know.
VB Code:
If Language = VBA Then
Hack = noob
End If
Re: Disabling Or Removing The Save Function In A Spreadsheet
Just poped in for a couple of minutes.
VB Code:
If Language = VBA Then
VBA ~ VB6
Else
MsgBox "???"
End If
Re: Disabling Or Removing The Save Function In A Spreadsheet
Quote:
Originally Posted by RobDog888
Ok. I'm looking around the spreadsheet now. Where do I find a code window to try some of this stuff? :blush:
Re: Disabling Or Removing The Save Function In A Spreadsheet
Press Alt+F11 to go the VBA IDE Editor. Or Tools > Macro > Visual Basic Editor.
Re: Disabling Or Removing The Save Function In A Spreadsheet
Quote:
Originally Posted by RobDog888
Press Alt+F11 to go the VBA IDE Editor. Or Tools > Macro > Visual Basic Editor.
Ohhhhh...I saw Macro under Tools but didn't go any further 'cause I'm not writing one.
Thanks.
Re: Disabling Or Removing The Save Function In A Spreadsheet
if i remember right you can create a macro to replace a menus default one by giving it the same name. though this goes back to wordbasic days, the functionality of it is still there, or you can just drag the menu item out of the menu
pete
also you might need to disable ctrl S
Re: Disabling Or Removing The Save Function In A Spreadsheet
i know by now you may well have finished this, but i found this as another solution for disabling save, so i though i would post it anyway in case it is useful
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel as Boolean)
a = MsgBox("Do you really want to save the workbook?", vbYesNo)
If a = vbNo Then Cancel = True
End Sub
pete