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

    Everythng is Grey (XL97; SR2)

    I'm using the following code and have encountered problems where all open file save buttons are greyed out. Is it possible to only greyout the save button with a specific file name?

    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

    Thanks,
    John

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Everythng is Grey (XL97; SR2)

    Are you saying that you only want to prevent the user from saving the workbook with only one file name? If that is what you are trying to do, why not put a file in the appropriate directory, with the appropriate name and make the file read only.

    The other alternative would be to use the Workbook BeforeSave event routine to intercept the save and then you use the GetSaveAsFileName to get the file name from the user and then if it is not the banned file name, you do the save and cancel the Excel save.
    Legare Coleman

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

    Re: Everythng is Grey (XL97; SR2)

    Legare,

    To clarify, say a user has 3 workbooks open and then opens a fourth containing the code mentioned above. If the user switches from one workbook to another the code pertains to all open workbooks not just the one in which the code resides. Another way to put it is the workbook with the code is active then the code is active otherwise it should not be envoked.

    John

    PS
    I did come up with the read only solution but as you know "inquiring minds" need to know if there is an alternate solution.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Everythng is Grey (XL97; SR2)

    You could place your code in the Workbook_WindowActivate event rather that Workbook Open, and reverse it in the Workbook_WindowDeactivate event, so that it only applies whilst the concerned workbook is activated.

    Or you could use <pre> Private Sub Workbook_BeforeSave(ByVal _
    SaveAsUI As Boolean, _
    Cancel As Boolean)
    If SaveAsUI = False Then
    MsgBox "Save Disabled"
    Cancel = True
    End If
    End Sub</pre>


    If you want to disable SaveAs also, just remove the If construct.

    Andrew C

Posting Permissions

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