Disabling "Save As..."

[JanetW]

I keep having problems with users using Save As instead of Save to sav
their work in a shared workbook, and for some reason occassionaly en
up saving the file in an older file format (which wipes out th
sharing, data validation, etc). Is there anyway to disable the Save A
function so that is is not available while this file is open?

Thanks

 

[Ron de Bruin]

One way if you use a English Excel version
Copy this in the thisworkbook module.

Remember this is only working if you enabled macro's
when you open the workbook

Private Sub Workbook_Activate()
Application.CommandBars("Worksheet Menu Bar").Controls("File") _
..Controls("Save &As...").Enabled = False
End Sub

Private Sub Workbook_Deactivate()
Application.CommandBars("Worksheet Menu Bar").Controls("File") _
..Controls("Save &As...").Enabled = True
End Sub

 

[Doug Kanter]

I'm looking at the Visual Basic help system for Excel 2000. It says:

"In MS Office, toolbars, menu bars and shortcut menus are all controlled
programmatically as one type of object: command bars.....All the following
items are represented in Visual Basic for Applications by CommandBar
objects:

Menu bars, toolbars and shortcut menus.
Menus on menu bars and toolbars.
Submenus on menus, submenus and shortcut menus.

You can modify any built-in menu bar or toolbar....."

That's an excerpt, but enough to whet your appetite for revenge against the
ignorant putzes in your office. But seriously, you can use VBA to
reprogram the menus, and attach the code you write to the workbook so it
changes the menus when the file is opened, and puts them back to normal when
the file is closed. Your next step is to spend an hour at Barnes & Noble,
looking through VBA books, unless someone here can recommend a good one.
When you've completed your mission, you're gonna feel really cool and your
coworkers will fear you, as they should.

 

[Joanne Ramaekers]

This isn't the perfect option, but may do. You can go
into Tools, Customise and drag the Save As option off the
File menu. However this can always be put back on by the
user.

Regards
Joanne

 

[JanetW]

*When you've completed your mission, you're gonna feel really coo
and your coworkers will fear you, as they should.
*

Yes, as it should be!!

Another poster has given some VB code that I will try (thanks Ron)

 

[JanetW]

Cool, thanks!

Will that toggle the Save As function on/off if they switch betwee
active workbooks? I'll give it a try

 

[Ron de Bruin]

Will that toggle the Save As function on/off if they switch between

active workbooks?

Yes

 

[Doug Kanter]

Yes, as it should be!!

Another poster has given some VB code that I will try (thanks Ron).

I prefer more complicated methods which cause one to ignore one's family for
two weeks while learning something new, but I think Ron's method will work,
too.

 

[Anirban]

Use the following code in ThisWorkBook module


Private Const FILE_MENU_ITEM As String = "Save &As..."

Private Sub Workbook_Open()
Set objCmdBrPp = Application.CommandBars("Worksheet Men
Bar").Controls("File")
Set objCmdBtn = objCmdBrPp.Controls.Item(6)

If objCmdBtn.Caption = FILE_MENU_ITEM Then

With objCmdBtn
.Enabled = False
End With
Else
' Item not found
End If
End Su

 

[JanetW]

Ok, I'm going to claim VB ignorance here. I can make a worksheet jum
through hoops, but usually shy away from "resorting" to VB.

I put in the two subroutines and Ron provided ["Private Su
Workbook_Activate()" and "Private Sub Workbook_Deactivate()], and the
work fine when I manually run them. The Help suggests these wil
execute as the workbook is switched to and from, which is exactly wha
I want. (Execpt Help also says Deactivate won't execute when the objec
is unloaded...does that mean when the workbook is closed?)

This is probably a really obvious thing, but how exactly do I get thes
subs to load and be active while the workbook is open? It was suggeste
that it only works if you enable macros when the file is opened, bu
since I don't get that option I assume macros are enabled. Do I need t
save them in a particular place, or Call them from an Auto_Ope
subroutine or something like that?

Thanks so much

 

[Ron de Bruin]

Hi Janet

I wrote this

Copy this in the Thisworkbook module.

1) Copy the code
2) Right click on the Excel icon next to File in the menu bar
3) Choose view code
4) Paste the code
5) Alt-Q to go back to Excel

The code will run if you go to a other workbook(or close it) and
it will run if you open the workbook or activate the workbook again



--
Regards Ron de Bruin
http://www.rondebruin.nl

Ok, I'm going to claim VB ignorance here. I can make a worksheet jump
through hoops, but usually shy away from "resorting" to VB.

I put in the two subroutines and Ron provided ["Private Sub
Workbook_Activate()" and "Private Sub Workbook_Deactivate()], and they
work fine when I manually run them. The Help suggests these will
execute as the workbook is switched to and from, which is exactly what
I want. (Execpt Help also says Deactivate won't execute when the object
is unloaded...does that mean when the workbook is closed?)

This is probably a really obvious thing, but how exactly do I get these
subs to load and be active while the workbook is open? It was suggested
that it only works if you enable macros when the file is opened, but
since I don't get that option I assume macros are enabled. Do I need to
save them in a particular place, or Call them from an Auto_Open
subroutine or something like that?

Thanks so much!

 

[JanetW]

Got it, thanks! I missed the "thisworkbook module" part...must have
sub-conciously processed it as a typo.

Works like a charm!