Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating sub menu in Command bar | Excel Programming | |||
Creating sub menu in Command bar | Excel Programming | |||
Creating Name for a Command Button | Excel Programming | |||
creating a dos command in VBE | Excel Programming | |||
Creating Command button Conditionally | Excel Programming |