Disable menu & toolbar in excel

[Dina01]

Hello,

I am pretty new in programming with Excel. I created a little db.

I would like to disable the standar menu and toolbar fromthe users when they open my excel document and only display my customized tool bar.

I am able to display my toolbar, but how do I disable the main menu and toolbars..

Thanks

 

[Mikeb1970]

Hi Dina,

i don't think there is a way to disable the main menu toolbar, if there is, i also would like to know how to do this.
Now on disabling the toolbars, Excel uses a limited numbers of toolbars (19 with full install, not counting costumized toolbars)

You need code that starts running when the file is opened
therefore need to click on This workbook in the VBA editor
then change the object(left column) from general to workbook, procedure(right column) should be changed to open.

then copy and paste the next code

Application.CommandBars("Standard&quot.Visible = False

this will hide the standard toolbar when the file is opened

There is another thing you should consider, i would imagine that most users wont be very happy when you change their settings, so you should consider a way to restore the setting when closing the file.


Hope this helps

Mike

 

[Dina01]

Hey Mike,

Thanks I will try that......

 

[Loomah]

Hi
I'd just like to add a little something to this.

Using the visible property of the commandbar jsut does that - makes it visibe or not. This means that users can manually reintroduce the toolbars. If you use the Enabled property then they can't reset them manually.

The main menubar doesn't have the visible property so enabling/disabling it is the only way to deal with it.

e.g.

CommandBars("worksheet menu bar").Enabled = False
CommandBars("standard").Enabled = False

Regarding Mike's comments on resetting toolbars this is a cut down version of something I helped with in this forum way back when!! You will need to adapt it and not take it as a completed app to suit specific needs

In the Workbook module

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ExitView
End Sub

Private Sub Workbook_Open()
    InitView
End Sub

In a standard module

Sub InitView()
    SetWindow xlOn
    SetMenu
End Sub

Sub ExitView()
    SetWindow xlOff
    ZapMenu
End Sub

Sub SetMenu()
    Dim myBar As CommandBar
    Dim myButton As CommandBarButton
    ZapMenu
    Set myBar = CommandBars.Add(Name:="OCM", _
                Position:=msoBarTop, _
                MenuBar:=True)
    Set myButton = myBar.Controls.Add(msoControlButton)
        myButton.Style = msoButtonCaption
        myButton.Caption = "E&xit"
        myButton.OnAction = "ExitOCM"
        myBar.Protection = msoBarNoMove + msoBarNoCustomize
        myBar.Visible = True
End Sub

Sub ZapMenu()
    On Error Resume Next
        CommandBars("OCM").Delete
End Sub

Sub SetWindow(State)
    Static myOldState
    Application.ScreenUpdating = False
    On Error Resume Next
        If State = xlOn Then
            Application.DisplayFormulaBar = False
            Application.DisplayStatusBar = False
        'CHANGED TO SPECIFY XLOFF
        ElseIf State = xlOff Then
            Application.DisplayFormulaBar = True
            Application.DisplayStatusBar = True
        End If
End Sub

Sub ExitOCM()
    ' added lines to restore formula & status bars
    Application.DisplayFormulaBar = True
    Application.DisplayStatusBar = True
    ActiveWorkbook.Close (False)
End Sub

Enjoy
Happy Friday
;-)


If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244