Can you determine values of combobox on custom controlbar 2

[timbo44]

Hi,

I am trying to dynamically change a list in a combobox control on a custom commandbar based on the selection made in another custom combobox on the same commandbar. Is there anyway of clearing the entries in the second combobox and then adding entries to this combobox based on the selection made in combobox 1. Basically there is a range of numbers in combobox 1. I want to display the range of numbers from the one above the number selected to the total.

example in combobox 1

1
2
3
4
5
6
7
8
9

Select 4 so to display in combobox 2

5
6
7
8
9


any other links to information on using commandbars and controls will be gratefully recieved.

Many thanks

Timbo44

 

[SkipVought]

Hi,

How did you assign ListFillRange initially?

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[rmikesmith]

timbo44,

When you create your custom CommandBar, set the Tag property of the ComboBox controls you want to interact with. Now, in the procedure you assigned to the OnAction property of ComboBox1 you can set up a reference to ComboBox2 like this:

Set oComboBox2 = CommandBars.FindControl _
  (Tag:="ComboBox2")

Now you manipulate this in the usual ways, for example:

With oComboBox2
  .Clear
  For i = Combo1Value To MAX
    .Additem i
  Next i
End With

where Combo1Value is the user-selected number from ComboBox1 and MAX is some maximum value. Substitute your variables/values as appropriate.

ps. You can set a reference to ComboBox1 in the same fashion since you will likely need to get it's Value or other properties, depending on how you want to find the starting value for ComboBox2

Regards,
Mike

 

[combo]

Alternatively you can create custom class and use 'Change' event to transfer message between combos:

Public WithEvents OCBCSource As Office.CommandBarComboBox
Public OCBCTarget As Office.CommandBarComboBox

Private Sub OCBCSource_Change(ByVal Ctrl As Office.CommandBarComboBox)
With OCBCTarget
    .Clear
    For i = Val(OCBCSource.Text) To 9
        .AddItem i
    Next i
    .ListIndex = 1
End With
End Sub

Public oCB As Office.CommandBar
Public cCBCB1 As clsOfficeCBCombo
Public oCBCB1 As Office.CommandBarComboBox
Public oCBCB2 As Office.CommandBarComboBox

Sub Init()
Set oCB = Application.CommandBars.Add
Set cCBCB1 = New clsOfficeCBCombo
With oCB
    Set oCBCB1 = oCB.Controls.Add(Type:=msoControlComboBox)
    For i = 1 To 9
        oCBCB1.AddItem i
    Next i
    oCBCB1.ListIndex = 1
    Set oCBCB2 = oCB.Controls.Add(Type:=msoControlComboBox)
    For i = 1 To 9
        oCBCB2.AddItem i
    Next i
    oCBCB2.ListIndex = 1
    Set cCBCB1.OCBCSource = oCBCB1
    Set cCBCB1.OCBCTarget = oCBCB2
    oCBCB1.OnAction = "x"
    oCBCB2.OnAction = "y"
End With
oCB.Visible = True
End Sub

combo