Page 526 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

Set cb = c.Controls.Add(msoControlButton, 1)
cb.Tag = "Excel2k3 VBA Query Edit"
cb.Style = msoButtonCaption
cb.Caption = "Edit Query"
cb.OnAction = "ThisWorkbook.EditDatabaseQuery"
Set cb = c.Controls.Add(msoControlButton, 1)
cb.Tag = "Excel2k3 VBA Query Database"
cb.Style = msoButtonCaption
cb.Caption = "Database"
cb.OnAction = "ThisWorkbook.ShowDatabaseInfo"

End Sub
Normally, if this command bar doesn’t exist, the program will generate an error. However,
with error checking disabled, the temporary object c will remain, set to Nothing. If c is Noth­
ing, the command bar doesn’t exist and will be deleted. This step ensures that the command
bar is always created with the proper controls.
Once the program is certain that the command bar doesn’t exist, it creates a new floating bar,
which can be docked with the other command bars, as shown in Figure 24-1. The remaining
statements in the AddCommandBar routine merely add the combo box control, along with
the three control button controls used to manage the application.

Two of the actions associated with the command bar controls open user forms. The ShowDa­
tabaseInfo routine shown below opens the DBInfo user form as a modal form.

Private Sub ShowDatabaseInfo()
DBInfo.Show vbModal

End Sub
The EditDatabaseQuery routine does the same thing with the DBQuery form.

Private Sub EditDatabaseQuery()

DBQuery.Show vbModal
End Sub

Ending the Program

The Workbook_BeforeClose event calls the DeleteCommandBar routine to ensure that all the
changes it made to Excel’s menus are removed prior to Excel shutting down.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

DeleteCommandBar
Chapter 24
End Sub

500
Part 6: Excel and the Outside World: Collaborating Made Easy