Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Jun 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hiding menu items (Excel 2000)

    Hi All, how would one hide the Formula Bar option in the View menu for a given workbook. Similarly, how would you hide the Protect Workbook on the tools menu and the Save/Save As on the File menu.

    Thanks guys and gals
    --cat

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Hiding menu items (Excel 2000)

    Visibility of the formula bar and of menu items are application-wide settings, so you have to be very careful if you change these settings. If something goes wrong, the user might be left with invisible items.

    You switch the formula bar on and off in the View tab of Tools | Options... The Visual Basic code for this is

    Application.DisplayFormulaBar = False ' to hide
    or
    Application.DisplayFormulaBar = True ' to show

    The code to make a menu item (in)visible looks like this:

    Application.CommandBars("File").Controls("Save As...").Visible = False ' to hide
    or
    Application.CommandBars("File").Controls("Save As...").Visible = True ' to show

    Analogously for other menu items.

    If you want to do this selectively for a specific workbook, you need application level events. That is a rather advanced feature in VBA.

  3. #3
    Lounger
    Join Date
    Jun 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding menu items (Excel 2000)

    Thanks ... as for events ... that's the "Workbook_Open" and the "Before_Close" events, correct?
    --cat

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Hiding menu items (Excel 2000)

    Cat,

    Those events wouldn't be enough, for the user can switch between workbooks in Excel. I presume that you only want to hide several items when a specific workbook is active. If you hide items in Workbook_Open and unhide them in Before_Close, the items would stay hidden if the user switches to another workbook. You must handle it in the WindowActivate and WindowDeactivate events (in the ThisWorkbook module), for example:

    Private Sub Workbook_WindowActivate(ByVal Wn As Window)
    Application.CommandBars("File").Controls("Save As...").Visible = False
    End Sub

    Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
    Application.CommandBars("File").Controls("Save As...").Visible = True
    End Sub

    (I thought at first that it would have to be handled at the application level, but these workbook-level events seem to do the job.)

  5. #5
    Lounger
    Join Date
    Jun 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding menu items (Excel 2000)

    Hans, ... you're saving my hide today! I'm on a very tight deadline ... new job and all so I do want to impress. Unfortunately, or fortunately, I am a self-taught Excel & VB user. When I actually record my macros and then add to them, the viewer is subjected to "watching" the activities. Is there a way to disable that?
    Thanks, again.
    --cat

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Hiding menu items (Excel 2000)

    Application.screenupdating = false

    to not redraw the screen during the macro and

    Application.screenupdating = true

    to reenable it

    Steve

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Hiding menu items (Excel 2000)

    Set Application.ScreenUpdating = False at the start of a macro, and Application.ScreenUpdating = True at the end. It is wise to add error handling to macros that use this to ensure that the latter statement will be executed even if an error occurs, otherwise the user will be stuck with a non-updating Excel window. The "skeleton" for this is:

    Sub MyMacro()
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    ' your code goes here

    ExitHandler:
    Application.ScreenUpdating = True
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

Posting Permissions

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