Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Greying Out Save Button (XL97;SR2)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Greying Out Save Button (XL97;SR2)

    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

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Greying Out Save Button (XL97;SR2)

    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

  4. #4
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Greying Out Save Button (XL97;SR2)

    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.

    <A target="_blank" HREF=http://www.cpearson.com/excel.htm>http://www.cpearson.com/excel.htm</A>
    <A target="_blank" HREF=http://www.j-walk.com/ss/excel/index.htm>http://www.j-walk.com/ss/excel/index.htm</A>

    Brent

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Greying Out Save Button (XL97;SR2)

    Brent,

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

    John

  6. #6
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Greying Out Save Button (XL97;SR2)

    John,

    Place these in the This Workbook module.
    <pre>Private Sub Workbook_BeforeClose(Cancel As Boolean)
    TestShowSave
    End Sub
    </pre>


    <pre>Private Sub Workbook_Open()
    TestHideSave
    End Sub
    </pre>


    These would go in a module:
    <pre>Sub TestHideSave()
    Dim FileMenu As CommandBarControl
    Set FileMenu = CommandBars(1).FindControl(ID:=30002)
    FileMenu.Controls("&Save").Visible = False
    End Sub
    </pre>

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



    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.

  7. #7
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Greying Out Save Button (XL97;SR2)

    John,

    This Workbook
    <pre>Private Sub Workbook_BeforeClose(Cancel As Boolean)
    TestShowSave
    End Sub
    </pre>

    <pre>Private Sub Workbook_Open()
    TestHideSave
    End Sub
    </pre>


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

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


    Brent

    <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Greying Out Save Button (XL97;SR2)

    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

  9. #9
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Greying Out Save Button (XL97;SR2)

    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

  10. #10
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Greying Out Save Button (XL97;SR2)

    John,

    You may also find some useful material on working with commandbars <A target="_blank" HREF=http://msdn.microsoft.com/library/en-us/odeopg/html/deovrmanipulatingcommandbarscommandbarcontrolswith vbacode.asp?frame=true>here</A> (use the pane on the left to dig deeper into the chapter).
    Gary

  11. #11
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Greying Out Save Button (XL97;SR2)

    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.

  12. #12
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Greying Out Save Button (XL97;SR2)

    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

  13. #13
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Greying Out Save Button (XL97;SR2)

    John,

    You are very welcome.

    <img src=/S/read.gif border=0 alt=read width=19 height=33>
    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:
    <pre>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
    </pre>

    <pre>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
    </pre>



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

  14. #14
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Greying Out Save Button (XL97;SR2)

    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.

    <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>
    John

  15. #15
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Greying Out Save Button (XL97;SR2)

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    John,

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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •