Hiding menu bar in excel 1

[GrahamUNC]

Does anyone know how to hide the menu bar (ie File, Edit, View, etc.) in Microsoft Excel? Thanks in advance.

 

[DarkSun]

You can remove them using Tools-Customise. You should also be able to hide them in VBA. Why do you want to do this?

 

[GrahamUNC]

We've created a economic model that we want to make more presentable to a client. We plan on putting this model onto cd and would like it to look as professional as possible when they open it. So we would like it too look the least like an excel program as possible. Can you program a macro in visual basic that will tell excel to open with menu bars off when the client opens the model?

 

[DarkSun]

The problem you have is the toolbars are setup for the application not for each workbook.

I will look into it on the weekend and let you know what I find out.

It should be possible to have a macro in Workbook_Open() that hides all the menus. Then a macro in Workbook_close() which returns the menus to normal.

 

[LoNeRaVeR]

GrahamUNC:

This is an interesting request. I never really had a reason to remove all the menu items. It is possible, but not the way one might think. Excel won't let you make the bar invisible as with other bars using the normal code.

[tt]Application.CommandBars(1).Visible = False[/tt]

This generates a Run-Time Automation Error, but you can remove the menu items, so it appears that there isn't a bar.

The following code may be copied directly into a Module. The Autpen subroutine will remove the Menu Bars and other items that you may want removed. Delete any lines that include items you want to keep (i.e. Gridlines, Scrollbars, or Tabs). These items are file specific, so you could even remove them manually before you save the file. The menus, however, will remain removed until you add them back. I also included an Auto_Close subroutine that returns the menus the way they were before you removed them. After testing the code a few times, I found this to be very helpful.

[tt]Dim aRemove(84) As Integer
Dim iRemove As Integer
Sub Autpen()
With Application
.DisplayFormulaBar = False
.DisplayStatusBar = False
For iRemove = 1 To Application.MenuBars(xlWorksheet).Menus.Count
.MenuBars(xlWorksheet).Menus(1).Delete
Next iRemove
For iRemove = 2 To 84
If .CommandBars(iRemove).Visible = True Then
.CommandBars(iRemove).Visible = False
aRemove(iRemove) = 1
End If
Next iRemove
End With
With ActiveWindow
.DisplayGridlines = False
.DisplayHeadings = False
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
.DisplayWorkbookTabs = False
End With
End Sub
Sub Auto_Close()
With Application
For iRemove = 2 To 84
If aRemove(iRemove) = 1 Then
.CommandBars(iRemove).Visible = True
End If
Next iRemove
.MenuBars(xlWorksheet).Reset
End With
End Sub[/tt]

I hope that accomplishes what you were attempting to do.

Enjoy,

LoNeRaVeR

 

[LoNeRaVeR]

GrahamUNC:

It looks like my "neat" code is a bit jostled in order to fit in the confines of the column. If you have any troubles, please post again in this thread, and I'll put the code on my web site for you to copy.

Regards,

LoNeRaVeR

 

[DarkSun]

LoNeRaVeR,
Nice code. Don't forget to set the other setting back as well. StatusBar, Formula Bar, ScrollBars, etc.

 

[GrahamUNC]

I did have a little problem with the code. I kept getting errors with the lines of:

aRemove(iRemove)=1

and

If a Remove(iRemove) = 1 Then

Also, as DarkSun pointed out the other bars need to be reset so that everything returns to normal after the file is closed. I'll play around with it some more but if you have some free time, any and all assistance would be greatly appreciated. Thanks so much!

 

[LoNeRaVeR]

DarkSun:

Hey thanks... I've enjoyed reading some of your code, too!


GrahamUNC:

Please make sure the following lines are not each split into two lines as shown in the original code and it should work.
[tt]
If .CommandBars(iRemove).Visible = True Then
.CommandBars(iRemove).Visible = True
[/tt]

As for the items:
[tt]
.DisplayGridlines = False
.DisplayHeadings = False
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
.DisplayWorkbookTabs = False
[/tt]
All of these are workbook specific, so won't affect any other workbook files. Everything else will be reset. Please let me know if you require the additional code to check the status of these and add them back to "this" worksheet.

LoNeRaVeR

 

[LoNeRaVeR]

GrahamUNC:

Oops... Sorry, I didn't see that I had removed these separately. They will stay removed, unless added back. The following code doesn't include a check before it adds them, but anyone that uses Excel without them is crazy. X-)

Copy the following code over what you currently have for the [tt]Auto_Close[/tt] subroutine from the beginning and including the first [tt]For[/tt] statement. Leave the rest of the code as is.
[tt]
Sub Auto_Close()
With Application
.DisplayFormulaBar = True
.DisplayStatusBar = True
For iRemove = 2 To 84
[/tt]
Please let me know if you need anything else.

LoNeRaVeR

 

[GrahamUNC]

Great. Exactly what I was trying to do. Thanks so much for your help!

 

[GrahamUNC]

OK. I'm almost there. I wrote a couple of lines that would diable all the right click short cut menus. This works except for the right click menu on the Menu bar. You can still right click up there and add the toolbars back. Here's what I added. I didn't add it to Autpen and Auto_Close yet.

Sub DisableAllShortcut Menus()
Dim cb As CommandBar
For each cb in CommandBars
If cb.Type = msoBarTypePopup Then cb.Enabled = False
Next cb
End Sub

 

[LoNeRaVeR]

GrahamUNC:

Very nice. The following code almost puts my previous code to shame.
[tt]
Sub DisableAllShortcutMenus()
Dim cb As CommandBar
For Each cb In CommandBars
If cb.Type < 3 Then cb.Enabled = False
Next cb
End Sub
[/tt]
There's almost always a shorter, faster, and easier way. Now if we can just figure out how to prevent Ctrl+1 without protecting the sheet. Disabling the ALT key might not be a bad idea, either.

Regards,

LoNeRaVeR

 

[LoNeRaVeR]

GrahamUNC:

Look what I found:
[tt]
Application.OnKey &quot;^1&quot;, &quot;&quot;
[/tt]
Grinning,

LoNeRaVeR

 

[LoNeRaVeR]

GrahamUNC:

Just when you thought it couldn't get any better... :-Q

Sub Everything_Off()
Dim cb As CommandBar
For Each cb In CommandBars
If cb.Type < 3 Then cb.Enabled = False
Next cb
With ActiveWindow
.DisplayHeadings = False
.DisplayWorkbookTabs = False
End With
With Application
.DisplayFullScreen = True
.DisplayFormulaBar = False
.DisplayScrollBars = False
.DisplayStatusBar = False
End With
End Sub

WARNING!!!

If you don't know what you are doing, this code can get you into quite a bit of trouble...

Enjoy, LoNeRaVeR

 

[alforjj]

This is great stuff. Does anyone know how to do the same thing in Word 2000?

Thanks.