hiding toolbars question

  • Hi again,
    I have in my code when a specific workbook opens it hides all the normal toolbars & menu bars and only displays the custom one I made... But,,,, I can still just right click on the toolbar, hit customize and add them all back.. How do I prevent users from doing this?? Thanks so much!!!

  • Try putting this into your Workbook_Open Module

    Application.CommandBars("Toolbar List").Enabled = False

    On closing you would need to set it back to true.


  • I have one more question about this.. It works great, assuming I am in a separate session of excel.. But if I hit file-open and open a new excel file, the toolbars etc.. wil be hidden on the new file as well.. Is there a way to get this to only apply to one file, not all files open in excel??
    Here is the code I have...

    Private Sub Workbook_Open()
    Application.CommandBars.ActiveMenuBar.Enabled = False
    Application.CommandBars("custom 1").Enabled = True
    Application.CommandBars("standard").Enabled = False
    Application.CommandBars("formatting").Enabled = False
    ActiveWindow.DisplayWorkbookTabs = False
    Application.CommandBars("Toolbar List").Enabled = False
    End Sub

    Also have this for the close

    Private Sub Worksheet_BeforeClose(Cancel As Boolean)
    Application.CommandBars.ActiveMenuBar.Enabled = True
    Application.CommandBars("custom 1").Enabled = False
    Application.CommandBars("standard").Enabled = True
    Application.CommandBars("formatting").Enabled = True
    ActiveWindow.DisplayWorkbookTabs = True
    Application.CommandBars("Toolbar List").Enabled = True
    End Sub

  • Hi,

    You would need to add this

    Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
    Application.CommandBars("ToolBar List").Enabled = True
    End Sub

    And you would also need to put into the Workbook_WindowActivate module as well to turn it back to false.

    Also, you might look at adding some With statements in your code and maybe putting in a module with your procedures and use a Call to run them in the Workbook Level modules you desire.


    Sub OpenUp()

    With Application
    .CommandBars.ActiveMenuBar.Enabled = True
    .CommandBars("Custom 1").Enabled = True
    End With
    End Sub

    Then in your Workbook modules you would just call your routine

    Private Sub Workbook_Open()
    Call OpenUP
    End Sub

    Just a thought.


  • This part works great!! Thanks!

    Pretty new to this whole code writing,, what will this do differently than how I have it now... I don't really know what "with" commands do... I'm assuming it will be more effective and/or run cleaner?? maybe?? LOL.. Can you explain this section??? Thanks for all ur help!

  • With statements help in keeping your code cleaner and it can help in making your code run a bit faster.

    This site has an Excel Newsletter and these links are three of those dealing with Efficient code. Dave does a great job in explaining it.




    You can sign up for this newsletter for free and it gives you some good advice and tips.


  • one more question about hiding toolbars... is there a way instead of hiding toolbars, to only show the one you want? The reason I ask, is if someone were smart, they'd create a custom toolbar in their personal.xls, then when they get to the sheet w/ all these hidden toolbars, their custom one is not hidden.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!