Excel

Using workbook events to control the visibility of defined toolbars/menus

Ease of Use

Intermediate

Version tested with

2002, 2003 

Submitted by:

Killian

Description:

This example uses the workbook Activate event to force the display of the workbook's custom toolbar (hiding the other Excel toolbars) and the Deatcivate event to restore the user's original toolbar layout. 

Discussion:

When distrubuting custom workbooks/automation that have specific functionality, it is often helpful for the user if unessecary toolbars are hidden and just the custom toolbar is left showing. It is obviously vital that when the distributed workbook is closed or made inactive, the user's original toolbar layout is restored. This example saves the user's visible toolbar names on a hidden worksheet and then hides them when the workbook is activated. When it's deactivated, the values on the hidden sheet are used to retore the original toolbar states. 

Code:

instructions for use 

			
 '#########################################
 'code for standard module
Option Explicit 
 
Sub BuildToolBar() 
     'call from WorkBook_Open
     
    Dim Tbar As CommandBar 
    Dim btnNew As CommandBarControl 
    Dim i As Long 
     
     'delete any existing instances of the toolbar
    DeleteToolbar 
     
     'Add a new toolbar
    Set Tbar = CommandBars.Add(Name:="My XL App Toolbar") 
    Tbar.Visible = True 
     
     'add some control buttons
    For i = 1 To 8 
        Set btnNew = Tbar.Controls.Add(Type:=msoControlButton) 
        With btnNew 
            .Caption = "Button " & i 
            .Style = msoButtonIconAndWrapCaptionBelow 
            .OnAction = "DefaultAction" 
            .FaceId = 480 + i 
            .Width = 60 
        End With 
    Next i 
     
    Tbar.Position = msoBarTop 
    Tbar.Left = 0 
    Tbar.Protection = msoBarNoMove 
     
    Set btnNew = Nothing 
    Set Tbar = Nothing 
     
End Sub 
 
Sub DeleteToolbar() 
     'call from Workbook_BeforeClose
     
    Dim cb As CommandBar 
     
    For Each cb In CommandBars 
        If cb.Name = "My XL App Toolbar" Then 
            cb.Delete 
        End If 
    Next cb 
     
End Sub 
 
Sub DefaultAction() 
     
    MsgBox CommandBars.ActionControl.Caption & " was pressed." 
     
End Sub 
 
 'code for standard module - END
 '#########################################
 
 '#########################################
 'code for ThisWorkbook module
Option Explicit 
 
Private Sub Workbook_Open() 
    BuildToolBar 
End Sub 
 
Private Sub Workbook_BeforeClose(Cancel As Boolean) 
    DeleteToolbar 
End Sub 
 
Private Sub Workbook_Activate() 
     
    Dim Tbar As CommandBar 
    Dim count As Integer 
    Dim wsMenu As Worksheet 
     
     'initialise storage sheet
    Application.ScreenUpdating = False 
    Set wsMenu = ThisWorkbook.Sheets("Menu") 
    wsMenu.Cells.Clear 
     
     'Store current visible toolbars
    count = 0 
    For Each Tbar In Application.CommandBars 
        If Tbar.Type = msoBarTypeNormal Then 
            If Tbar.Visible Then 
                count = count + 1 
                Tbar.Visible = False 
                wsMenu.Cells(count, 1) = Tbar.Name 
            End If 
        End If 
    Next Tbar 
    Application.CommandBars("My XL App Toolbar").Visible = True 
    Application.ScreenUpdating = True 
    Set wsMenu = Nothing 
     
End Sub 
 
Private Sub Workbook_Deactivate() 
     
    Dim c As Range 
    Dim wsMenu As Worksheet 
     
     'initialise storage sheet
    Application.ScreenUpdating = False 
    Set wsMenu = ThisWorkbook.Sheets("Menu") 
     
     'show the previously displayed the toolbars
    On Error Resume Next 
    For Each c In wsMenu.Range("A:A") _ 
        .SpecialCells(xlCellTypeConstants) 
        Application.CommandBars(c.Value).Visible = True 
    Next c 
    Application.CommandBars("My XL App Toolbar").Visible = False 
    Application.ScreenUpdating = True 
     
    Set wsMenu = Nothing 
     
End Sub 
 
 'code for ThisWorkbook module - END
 '#########################################

How to use:

  1. Open the VBE in a new workbook
  2. Add a standard module (Insert>Module) and paste in the code (up to "code for standard module - END")
  3. Go to the WorkBook code module (Double click the "ThisWorkbook" item in the Project Explorer
  4. Paste in the code for the workbook events
  5. Rename a sheet in the workbook "Menu"
  6. In the Project Explorer, select the renamed sheet (e.g "Sheet1 (Menu)") and set its "Visible" property to 0 (xlSheetHidden)
  7. Save and close the workbook
 

Test the code:

  1. When th project workbook is active, the user should only have the default menu (Worksheet Menu Bar) and the custom toolbar, docked at the top and locked.
  2. When switching to other workbooks, the custom tolbar should be hidden and the users original toolbars restored.
 

Sample File:

CustomAppToolBar.zip 13.67KB 

Approved by mdmackillop


This entry has been viewed 229 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express