Workbook Open

Workbook_Open is the default workbook event. This procedure is activated when a workbook is opened—no user interface is required. It has a variety of uses, such as checking the username and then customizing the user's privileges in the workbook.

The following code checks the UserName and if it is not Admin, protects each sheet from user changes (UserlnterfaceOnly allows macros to make changes, but not the user):

Private Sub Workbook_Open() Dim sht As Worksheet

If Application.UserName <> "Admin" Then For Each sht In Worksheets sht.Protect UserInterfaceOnly:=True Next sht End If End Sub workbook.

Workbook_Open can also be used to create custom menus or toolbars. The following code adds the menu MrExcel Programs with two options underneath it (see Figure 8.3).

■ For more information on custom menus, see "Creating a Custom Menu," p.431,in Chapter 24.

Sub Workbook_Open()

Dim cbWSMenuBar As CommandBar

Dim Ctrl As CommandBarControl, muCustom As CommandBarControl Dim iHelpIndex As Integer

Set cbWSMenuBar = Application.CommandBars("Worksheet menu bar")

iHelpIndex = cbWSMenuBar.Controls("Help").Index

Set muCustom = cbWSMenuBar.Controls.Add(Type:=msoControlPopup, _

Before:=iHelpIndex, Temporary:=True) For Each Ctrl In cbWSMenuBar.Controls

If Ctrl.Caption = "&MrExcel Programs" Then cbWSMenuBar.Controls("MrExcel Programs").Delete End If Next Ctrl With muCustom

.Caption = "&MrExcel Programs" With .Controls.Add(Type:=msoControlButton) .Caption = "&Import and Format" .OnAction = "ImportFormat" End With

With .Controls.Add(Type:=msoControlButton) .Caption = "&Calculate Year End" .OnAction = "CalcYearEnd" End With End With End Sub

Figure 8.3

The Open event can be used to create custom menus.

MrExcel Progranc | Help S] Import and Fomat Tjlculale Year End

0 0

Post a comment