Disabling Or Removing The Save Function In A Spreadsheet

[Hack]

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?

[RobDog888]

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.

[Hack]

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?

[RobDog888]

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

[Hack]

Thanks Rob. I'll give it a shot an let you know.

If Language = VBA Then
   Hack = noob
End If

[RobDog888]

Just poped in for a couple of minutes.

If Language = VBA Then
    VBA ~ VB6
Else
    MsgBox "???"
End If

[Hack]

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

Ok. I'm looking around the spreadsheet now. Where do I find a code window to try some of this stuff?

[RobDog888]

Press Alt+F11 to go the VBA IDE Editor. Or Tools > Macro > Visual Basic Editor.

[Hack]

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.

[westconn1]

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

[westconn1]

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