Question about Command Bar References

[John Keith]

' new CB defined
Dim cbrBar As Office.CommandBar
Set cbrBar = Application.CommandBars.Add("New CB")
With cbrBar
.Visible = True
.Enabled = True
.Position = msoBarTop
End With

'1st control
Dim ctlControl As Office.CommandBarControl
Set ctlControl = cbrBar.Controls.Add(msoControlDropdown)
With ctlControl
.Tag = "OrderType"
.BeginGroup = True
.Caption = "Type:"
.Style = msoComboLabel
.Enabled = True
.TooltipText = "Choose the Order type."
.OnAction = "Toggle_Type"
.DropDownLines = 2
.ListHeaderCount = 0
.AddItem "Food"
.AddItem "Produce"
End With

'2nd Control
Set ctlControl = cbrBar.Controls.Add(msoControlDropdown)
With ctlControl
.Tag = "FromCycle"
.BeginGroup = True
.Caption = "From:"
.Style = msoComboLabel
.Enabled = True
.TooltipText = "Choose the starting cycle."
.OnAction = "Lookup_Cycle"
.DropDownLines = fromCyc.Count
.ListHeaderCount = 0
For Each rngCell In fromCyc.Cells
.AddItem rngCell.Value
Next rngCell
.ListIndex = 1
End With
************************************************
'In a seperate module of the same project...
Dim cbrBar As Office.CommandBar
Dim ctlToggle As Office.CommandBarControl
Dim ctlFromCycle As Office.CommandBarControl
Set cbrBar = Application.CommandBars("NewCB")
Set ctlToggle = cbrBar.Controls(1) 'OrderType
Set ctlFromCycle = cbrBar.Controls(2) 'FromCycle

** now for my question **
How can I "set" the reference to Controls for OrderType and FromCycle with
out having to use the (1)<-hardcoded item number?
Why won't something like:

Set ctlToggle = cbrBar.Controls("OrderType")
Set ctlFromCycle = cbrBar.Controls("FromCycle")

....assign the reference properly. (Or) What is the proper syntax to assign
the reference by the name (have I missed assigning a property that would
allow it?)

 

[quartz]

John,

You have already defined a tag for your controls, you can use that with
"FindControl" to get a reference to your controls. In the following example,
the variable "ctrl" is set to your "OrderCycle" control and the message box
then returns the position of the control on the toolbar (that's the 1 you
refer to in your original post):

Dim ctrl As CommandBarControl
Set ctrl = CommandBars.FindControl(Tag:="FromCycle")
MsgBox ctrl.Index

HTH/

 

[John Keith]

Thanks, that worked...

One other need though... how do you programmatically tell which of the
controls was changed. I have been using
ctlNum = Application.Caller(1) to grab the 1st item from the commandbar
array where the 1st bucket holds a number that "can" be used to determine
which control's value was changed. Is there some way to test by name to see
if the control changed?

I could make the .OnAction= different for each control, but that seems like
a "brute-force" method.

The ctlNum value is 0 when The control was not used to enter the progrm
(i.e. macro called another macro.
(first control is a simple button) which has its own routine to run
.... ctlNum is 3 when the 2nd control was changed
(3rd control is another button that has no .onaction set)
.... ctlNum is 6 when the 4th control was changed

Note: 2nd and 4th controls are dropdown lists, hence both call the same
..onAction I should be able to use the .tag here too I would think.

How can I determine the control that changes with out having to test the
..caller item? (which changes anytime the command bar has controls added or
deleted)

 

[quartz]

John,

I'm not sure I have a good answer for you on this. I normally assign each
control to its own program, that is the easiest and most straight forward
method. Another method I have used is to include a "very hidden" sheet in the
file where the program resides, then capture each control's value and write
it into specific cells in the sheet. You can then update these values and
compare them for changes.

HTH/

 

[John Keith]

K, that does seem easier anyway.

There was something I read about keeping the same onaction for controls on a
commandbar then using the tag in someway so that you "hook" events to the
control which then allows a before_xxx event; referencing both builtin and
custom through the same click event and COM addins. But I don't really
understand how all that would work. Since I dont really need the extra
features... I'll just go with different .onaction properties.

thanks