Hiding menu items (Excel 2000)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

Thanks … as for events … that’s the “Workbook_Open” and the “Before_Close” events, correct?
–cat

Reply | Quote

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.)

Reply | Quote

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

Reply | Quote

Application.screenupdating = false

to not redraw the screen during the macro and

Application.screenupdating = true

to reenable it

Steve

Reply | Quote

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

Reply | Quote