Greying Out Save Button (XL97;SR2)

Topic

New Reply

I would like to grey out the file save button as well as the file save option. I do not mind end-users saving the file via “save as” but do not want them to be able to save to the original location. Any ideas how?

Thanks,
John

Reply | Quote

Replies

If this is just for one file then you can set the Read Only property for your document when saving it. (Set a password to allow file modification).

StuartR

Reply | Quote

Stuart,

Thanks for the reply. I am aware of that particular save feature in XL and have chosen not to use it. I’m looking for a solution that will grey out the file save button and drop down option..

John

Reply | Quote

you would write a macro disabling the desried command and place it in the workbook open event and then a macro to restore excel and place that one in the workbook close event.

both of these sites are full of excellent information on all things excel.

http://www.cpearson.com/excel.htm
http://www.j-walk.com/ss/excel/index.htm

Brent

Reply | Quote

Brent,

What you say makes sense. What commands are associated with the “save button”? I could not find any documentation.

John

Reply | Quote

John,

Place these in the This Workbook module.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    TestShowSave 
End Sub

Private Sub Workbook_Open()
    TestHideSave
End Sub

These would go in a module:

Sub TestHideSave()
    Dim FileMenu As CommandBarControl
    Set FileMenu = CommandBars(1).FindControl(ID:=30002)
    FileMenu.Controls("&Save").Visible = False
End Sub

Sub TestShowSave()
    Dim FileMenu As CommandBarControl
    Set FileMenu = CommandBars(1).FindControl(ID:=30002)
    FileMenu.Controls("&Save").Visible = True
End Sub

You still need to trap the Ctrl + S key combo.

This worked in a module but when I placed the code in the This Workbook mod it had a fit so I put the code back in a seperate mod and call them from the This Workbook. I have to go help someone move…I will check back tomorrow to see how it is going.

Reply | Quote

John,

This Workbook

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    TestShowSave
End Sub

Private Sub Workbook_Open()
    TestHideSave
End Sub

Module1

Sub TestHideSave()
    Dim FileMenu As CommandBarControl
    Set FileMenu = CommandBars(1).FindControl(ID:=30002)
    Application.OnKey "^{s}", ""
    FileMenu.Controls("&Save").Visible = False
End Sub

Sub TestShowSave()
    Dim FileMenu As CommandBarControl
    Set FileMenu = CommandBars(1).FindControl(ID:=30002)
    Application.OnKey "^{s}"
    FileMenu.Controls("&Save").Visible = True
End Sub

Brent

Reply | Quote

Brent,

The code is just what I needed. I’m not familiar with the commandbars & findcontrols but I am certainly going to learn more about them.

Is “&Save” associated with ID:30002? What resource would you recommend to learn more about the commandbars & findcontrols? I have looked at jwalker’s FaceID values for the commandbar images and determined that the FaceID for the “SAVE Button” = 3 but don’t know how to use it in the code (to grey out the button).

Thanks,
John

Reply | Quote

John,

John Walkenbach has written many books on each of the various flavors of Excel. When I run up against something that I am not certain of, I look up what it is that I am trying to accomplish in one of his books and tweak the examples that he gives until it does what I want it to do.

I also read, each morning and through the day, the various boards here at Woody’s for ideas and search for help or clarification here and/or at several other sites for source code which I then mutilate until things function something similar to what I envisioned.

As all my books are at work and I am now at home relaxing with a cold one, I will get back with you tomorrow.

Note: The Excel board is also an excellent source for information and help.

Brent

Reply | Quote

Brent,

Thanks for pointing me to the various references.

After reviewing the material I never came across information relative to greying out or hiding the “Save Button”. Most of the information relates to hiding the command bar(s). Any ideas?

Thanks,
John

Reply | Quote

John,

You are very welcome.

I am always looking for information, howto’s, anything to spark an idea. With all this information available often times I will still not find what I am searching for or not exactly what I need. At these times I try to read between the lines and hack away till I make it work. This is how I have learned most of what little I know…by trying and trying and slamming my head on the desk and pulling of hair and then getting a cup of coffee and trying again. The code may not end up looking very pretty but it WORKS.

So I took Brooks solution and hacked it and came up with this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    With Application
        .CommandBars("Worksheet Menu Bar").Controls("&File").Controls("&Save").Enabled = True
        .CommandBars("Standard").Controls(3).Enabled = True
        .OnKey "^{s}"
    End With
End Sub

Private Sub Workbook_Open()
    With Application
        .CommandBars("Worksheet Menu Bar").Controls("&File").Controls("&Save").Enabled = False
        .CommandBars("Standard").Controls(3).Enabled = False
        .OnKey "^{s}", ""
    End With
End Sub

…i pulled this post twice to correct spelling…you think i would use the spell check….big dummy

Reply | Quote

Brent,

Thank you for you assistance. I’ve learned quite a bit from the code you provided. Between you and Brooke I know enough to get deeper in trouble.

John

Reply | Quote

John,

Trouble is my middle name…I keep getting in way over my head…and somehow I always make it to the surface.

Reply | Quote

Welcome to the club.

Reply | Quote

Brent,

Now that I’ve dug my self deeper. I need your assistance once again. I modified the code so graciously provided by Brooke and tweaked by you to basically restrict the end-user from saving the file. Ultimately they would only have the capability of closing the file without saving. My problem is that I can not run the code without encountering an object command button failure when trying to delete the new button. Here is my code:

THIS WORKBOOK CODE

Private Sub Workbook_Open()
With Application
.CommandBars(“Worksheet Menu Bar”).Controls(“&File”).Controls(“&Save”).Enabled = False
.CommandBars(“Worksheet Menu Bar”).Controls(“&File”).Controls(“&Close”).Enabled = False
.CommandBars(“Standard”).Controls(3).Enabled = False
.OnKey “^{s}”, “”
End With
AddNewMenuItem
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Application
.CommandBars(“Worksheet Menu Bar”).Controls(“&File”).Controls(“&Save”).Enabled = True
.CommandBars(“Worksheet Menu Bar”).Controls(“&File”).Controls(“&Close”).Enabled = True
.CommandBars(“Standard”).Controls(3).Enabled = True
.OnKey “^{s}”
UnCheckButton
DeleteNewMenuItem
End With
End Sub

Private Sub AddNewMenuItem()
Dim CmdBar As CommandBar
Dim CmdBarMenu As CommandBarControl
Dim CmdBarMenuItem As CommandBarControl
Set CmdBar = Application.CommandBars(“Worksheet Menu Bar”) ‘ Point to the Worksheet Menu Bar
Set CmdBarMenu = CmdBar.Controls(“File”) ‘ Point to the File menu on the menu bar
Set CmdBarMenuItem = CmdBarMenu.Controls.Add ‘ Add a new menu item to the Tools menu
‘ Set the properties for the new control
With CmdBarMenuItem
.Caption = “Exit This WorkBook”
.OnAction = “‘” & ThisWorkbook.Name & “‘!ExitXLS”
.Tag = “SomeString”
End With
End Sub

Sub DeleteNewMenuItem()
Dim CmdBar As CommandBar
Dim CmdBarMenu As CommandBarControl

Set CmdBar = Application.CommandBars(“Worksheet Menu Bar”)
Set CmdBarMenu = CmdBar.Controls(“File”)
‘CommandBars(1).Commands(“File”).Commands(“Exit This WorkBook”).State = msoButtonUp
‘ CmdBarMenu.Controls(“Exit This WorkBook”).State = msoButtonUp
CmdBarMenu.Controls(“Exit This WorkBook”).Delete
End Sub

Sub UnCheckButton()
Dim CmdBar As CommandBar
Dim CmdBarMenu As CommandBarControl

Set CmdBar = Application.CommandBars(“Worksheet Menu Bar”)
Set CmdBarMenu = CmdBar.Controls(“File”)
‘CommandBars(1).Commands(“File”).Commands(“Exit This WorkBook”).State = msoButtonUp
CmdBarMenu.Controls(“Exit This WorkBook”).State = msoButtonUp
End Sub

MODULE CODE

Sub ExitXLS()
Application.DisplayAlerts = False
ActiveWindow.Close
End Sub

Thank you once again,
John

Reply | Quote

I think that what is going on here is that you are trying to delete the button whilst it is still in use. And excel won’t let you do that. I’ve been playing with this for a bit and can’t see a way round the problem. Lets hope someone else can jump in with a more constructive response.

Reply | Quote

Brooke,

I may have a workaround. I believe I read somewhere that one can change the code behind the dropdown option. If this is true, then I could change the “Close” code upon opening the workbook and reset the “Close” default upon exiting the workbook.. Unfortunately for me I can not remember where I read the documentation.

John

Reply | Quote

A simple resolution to my problem would be to add “ThisWorkbook.Saved = True” to the Private Sub Workbook_BeforeClose(Cancel As Boolean). There would be no need to add an new menu item to the existing command bar as this piece of code fools XL into thinking that the file was already saved.

John

Reply | Quote

John,

You may also find some useful material on working with commandbars here (use the pane on the left to dig deeper into the chapter).
Gary

Reply | Quote

I think you’ll find that playing about with the FaceID won’t enable you to get what you want.

FWIW, in 2k the following works for me.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars(“Worksheet Menu Bar”).Controls(“&File”).Controls(“&Save”).Enabled = True
Application.OnKey “^{s}”
End Sub

Private Sub Workbook_Open()
Application.CommandBars(“Worksheet Menu Bar”).Controls(“&File”).Controls(“&Save”).Enabled = False
Application.OnKey “^{s}”, “”
End Sub

If like Brent you have problems with this, use the trick he used above to move the guts out to a code module.

Reply | Quote