Custom Toolbar Drop Down

[Cory]

I have a document that is linked to an Excel data file (the data in the Excel
file is date specific by year and month). I have a custom toolbar in Word
that has buttons to update the Excel data, but I would like to add two drop
down boxes (year and month) to my toolbar so that the user can select the
date that the data should be refreshed for. I know that I could add a combo
box within the document, but I'm trying to avoid that. If this can be done,
I'd appreciate the help. Thanks.

 

[Jean-Guy Marcil]

Cory was telling us:
Cory nous racontait que :

I have a document that is linked to an Excel data file (the data in
the Excel file is date specific by year and month). I have a custom
toolbar in Word that has buttons to update the Excel data, but I
would like to add two drop down boxes (year and month) to my toolbar
so that the user can select the date that the data should be
refreshed for. I know that I could add a combo box within the
document, but I'm trying to avoid that. If this can be done, I'd
appreciate the help. Thanks.

Here is an example on how to create a combobox on the toolbar:

Sub AddComboBoxStyles()
'To be run once, to create the toolbar dropdown button
'Change the number in the "Set cbo =" statement to reflect the position
'of the created button on the toolbar
Dim cbo As Office.CommandBarComboBox

Set cbo = CommandBars(ToolBarName). _
Controls.Add(msoControlComboBox, , , 2)

With cbo
.Caption = "Your Caption"
'when user selects an item in the list,
'this macro will execute:
.OnAction = "Your Macro"
.Width = 132
'0 Means the the drop down will accomodate the content or
'as many lines as necessary
.DropDownLines = 15
.AddItem "Your first item"
.AddItem "etc."
End With

End Sub


--

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Cory]

Perfect. Merci beaucoup!

Cory was telling us:
Cory nous racontait que :


Here is an example on how to create a combobox on the toolbar:

Sub AddComboBoxStyles()
'To be run once, to create the toolbar dropdown button
'Change the number in the "Set cbo =" statement to reflect the position
'of the created button on the toolbar
Dim cbo As Office.CommandBarComboBox

Set cbo = CommandBars(ToolBarName). _
Controls.Add(msoControlComboBox, , , 2)

With cbo
.Caption = "Your Caption"
'when user selects an item in the list,
'this macro will execute:
.OnAction = "Your Macro"
.Width = 132
'0 Means the the drop down will accomodate the content or
'as many lines as necessary
.DropDownLines = 15
.AddItem "Your first item"
.AddItem "etc."
End With

End Sub


--

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Greg Maxey]

JGM,

I am not that experienced with toolbars, but I scratched this together
as an example of how to do something unique with each item selected in
the combobox:

Sub Your_macro()
Dim myCB As CommandBarComboBox
Dim i As Long
Set myCB = Application.CommandBars("TollbarName").Controls(2)
i = myCB.ListIndex
Select Case i
Case 1
MsgBox "Call code here for first item"
Case 2
MsgBox "Call Code here for second item"
Case Else
Selection.InsertAfter myCB.Text
End Select
End Sub

If this is off the mark then please advise of the preferred method.
Thanks.

 

[Jean-Guy Marcil]

Greg Maxey was telling us:
Greg Maxey nous racontait que :

JGM,

I am not that experienced with toolbars, but I scratched this together
as an example of how to do something unique with each item selected in
the combobox:

Sub Your_macro()
Dim myCB As CommandBarComboBox
Dim i As Long
Set myCB = Application.CommandBars("TollbarName").Controls(2)
i = myCB.ListIndex
Select Case i
Case 1
MsgBox "Call code here for first item"
Case 2
MsgBox "Call Code here for second item"
Case Else
Selection.InsertAfter myCB.Text
End Select
End Sub

If this is off the mark then please advise of the preferred method.
Thanks.

Sure, if you need a different action depending on the item selected your
code will work.
Or, if the order may change, but not the values, you may act according to
the text (Or parts of the text by using Left, Right, Mid, etc.):

Dim myCB As CommandBarComboBox
Dim strSelected As String
Set myCB = Application.CommandBars("Test").Controls(1)
strSelected = myCB.Text
Select Case strSelected
Case "Your first item"
MsgBox "Call code here for first item"
Case "Your second item"
MsgBox "Call Code here for second item"
Case Else
Selection.InsertAfter myCB.Text
End Select

I use something this when I build text blocks on the fly (From user info in
a userform for example). I use the Text property to know which text block to
insert at the cursor location.

Another useful command is:
Application.CommandBars.ActionControl.Caption
to return the caption of the button that was clicked. Not useful for a
dropdown thought because it does not return the text of the item selected,
but the hidden caption of the dropdown itself. But it is useful for regular
toolbar buttons.

--

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org