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.


    Bruce

    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.


    Example


    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.


    Bruce


    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.


    http://www.ozgrid.com/News/ExcelSubtotalsEfficientVBA.htm


    http://www.ozgrid.com/News/NestingFunctionsFastVBACode.htm


    http://www.ozgrid.com/News/NestingFunctions2FastVBACode3.htm


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


    Bruce

    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!

Register Yourself Login