Floating Toolbar with Combobox

M

MJK

Board Regular
Joined
Dec 4, 2002
Messages
179
I'm having problems executing macros using a Combobox within Floating Toolbar. I was successful using the AddItem command to populate the dropdown list. I wrote a separate procedure to execute the macros. Its strange but when "First Item", "Second Item" or "Third Item are selected, they all execute Macro3.

Sub Cbar()
On Error Resume Next
Set myBar = CommandBars.Add("Reports")
With myBar.Controls.Add(msoControlComboBox)
.Caption = "Special Item Report"
.AddItem "First Item", 1
.OnAction = "First_Item"
.AddItem "Second Item", 2
.OnAction = "Second_Item"
.AddItem "Third Item", 3
.OnAction = "Third_Item"
.DropDownLines = 10
.DropDownWidth = 75
.ListHeaderCount = 0
.Caption = "Special Item Report"
.FaceId = 265
End With
Set newcombo = CommandBars("Custom")
Public Sub First_Item()
Call Macro1
End Sub
Public Sub Second_Item()
Call Macro2
End Sub
Public Sub Third_Item()
Call Macro3
End Sub

Any help would be greatly appreciated

MJK
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
In standard module...

<font color="#0000A0">Option</font> <font color="#0000A0">Explicit</font>

<font color="#0000A0">Sub</font> Cbar()
       <font color="#0000A0">Dim</font> MyBar <font color="#0000A0">As</font> CommandBar
       <font color="#0000A0">On</font> <font color="#0000A0">Error</font> <font color="#0000A0">Resume</font> <font color="#0000A0">Next</font>
       CommandBars("Reports").Delete
       <font color="#0000A0">On</font> <font color="#0000A0">Error</font> <font color="#0000A0">GoTo</font> 0
       <font color="#0000A0">Set</font> MyBar = CommandBars.Add("Reports")
       <font color="#0000A0">With</font> MyBar.Controls.Add(msoControlComboBox)
             .Caption = "Special Item Report"
             .AddItem "First Item", 1
             .AddItem "Second Item", 2
             .AddItem "Third Item", 3
             .DropDownLines = 10
             .DropDownWidth = 75
             .ListHeaderCount = 0
             .Caption = "Special Item Report"
             .OnAction = "ClickCombo"
       <font color="#0000A0">End</font> <font color="#0000A0">With</font>
       MyBar.Visible = <font color="#0000A0">True</font>
<font color="#0000A0">End</font> <font color="#0000A0">Sub</font>

<font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> ClickCombo()

       <font color="#0000A0">Select</font> <font color="#0000A0">Case</font> CommandBars.ActionControl.Text
             <font color="#0000A0">Case</font> "First Item"
                   <font color="#0000A0">Call</font> Macro1
             <font color="#0000A0">Case</font> "Second Item"
                   <font color="#0000A0">Call</font> Macro2
             <font color="#0000A0">Case</font> "Third Item"
                   <font color="#0000A0">Call</font> Macro3
       <font color="#0000A0">End</font> <font color="#0000A0">Select</font>
      
      <font color="#008000"> 'or...</font>
      
       <font color="#0000A0">Select</font> <font color="#0000A0">Case</font> CommandBars.ActionControl.ListIndex
             <font color="#0000A0">Case</font> 1
                   <font color="#0000A0">Call</font> Macro1
             <font color="#0000A0">Case</font> 2
                   <font color="#0000A0">Call</font> Macro2
             <font color="#0000A0">Case</font> 3
                   <font color="#0000A0">Call</font> Macro3
       <font color="#0000A0">End</font> <font color="#0000A0">Select</font>

<font color="#0000A0">End</font> <font color="#0000A0">Sub</font>

<font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> Macro1()
       MsgBox "First Item Clicked"
<font color="#0000A0">End</font> <font color="#0000A0">Sub</font>

<font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> Macro2()
       MsgBox "Second Item Clicked"
<font color="#0000A0">End</font> <font color="#0000A0">Sub</font>

<font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> Macro3()
       MsgBox "Third Item Clicked"
<font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
VBA works on work PC but not home PC
Replies
3
Views
2K
rollis13
rollis13
A
  • Question
Combobox Not Populating With RowSource Set in The Control's Properties
Replies
0
Views
355
Ark68
A
O
  • Question
Custom Toolbar button not running full sub
Replies
1
Views
668
ollienashchapman
O
B
  • Question
Optimize Search Function
Replies
1
Views
625
BenjaminLyon
B
W
  • Question
Multiple ComboBox help
Replies
1
Views
606
dmt32
D

Forum statistics

Threads
1,215,333
Messages
6,124,317
Members
449,153
Latest member
JazzSingerNL

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top