creating command bar
 

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

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

creating command bar
 

Thanks Bob.

First one works great.

Second one doesnt. Through the existing code I had I have created two
buttons. How do I then assign macros to these buttons?

Cheers
Matthew Balch
"Bob Phillips" wrote:

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

creating command bar
 

I have found the answer myself.

..OnAction.......... etc

Thanks

"Bob Phillips" wrote:

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