creating command bar
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Titles Database").Delete
End Sub
Private Sub Workbook_Open()
CreateCommandBarWithControls
End Sub
'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"Matthew Balch" wrote in message
...
Hi,
Ive looked up the help and created a custom toolbar which is created upon
opening the workbook.
How do I:-
1) Destory this toolbar on closing of the workbook.
2) Assign macros to the buttons created.
I have this copied from an example:-
Public Sub CreateCommandBarWithControls()
' Purpose: Creates a sample command bar with a number
' of controls.
Dim objCommandBar As Office.CommandBar
Dim objCommandBarControl As Office.CommandBarControl
Dim objCommandBarButton As Office.CommandBarButton
Dim objCommandBarComboBox As Office.CommandBarComboBox
Dim objCommandBarPopup As Office.CommandBarPopup
For Each objCommandBar In Application.CommandBars
If objCommandBar.Name = "Titles Database" Then
objCommandBar.Delete
End If
Next objCommandBar
Set objCommandBar = Application.CommandBars.Add _
("Titles Database")
With objCommandBar.Controls
' Set objCommandBarComboBox = .Add(msoControlDropdown)
' With objCommandBarComboBox
' .AddItem "Index"
' .AddItem "Check Sheet"
' .AddItem "Summary"
' .Style = msoComboLabel
' .Caption = "Sheet Selection"
' End With
Set objCommandBarButton = .Add(msoControlButton)
With objCommandBarButton
.Caption = "Refresh Data"
.Style = msoButtonIconAndCaption
.TooltipText = _
"Press to restablish links and refresh data."
End With
Set objCommandBarButton = .Add(msoControlButton)
With objCommandBarButton
.Caption = "An extra"
.Style = msoButtonIconAndCaption
End With
End With
objCommandBar.Visible = True
End Sub
|