I have activex combobox on a worksheet. Is there a way to return the activex combox name in the change event?
This returns the name
Code: Select all
Debug.Print shTarget.OLEObjects("Choice1").Name
Code: Select all
Debug.Print shTarget.OLEObjects("Choice1").Name
Code: Select all
Private Sub Choice1_Change()
For n = 2 To maxOptions
shTarget.OLEObjects("Choice" & n).Object.Clear
Next n
If Choice1.ListIndex > -1 Then Choice2.List = Split(FillList(1), ",")
End Sub
Private Sub Choice2_Change()
For n = 3 To maxOptions
shTarget.OLEObjects("Choice" & n).Object.Clear
Next n
If Choice2.ListIndex > -1 Then Choice3.List = Split(FillList(2), ",")
End Sub
Private Sub Choice3_Change()
For n = 4 To maxOptions
shTarget.OLEObjects("Choice" & n).Object.Clear
Next n
If Choice3.ListIndex > -1 Then Choice4.List = Split(FillList(3), ",")
End Sub
Code: Select all
Private Sub Choice1_Change()
Call HandleChoice(1)
End Sub
Private Sub Choice2_Change()
Call HandleChoice(2)
End Sub
Private Sub Choice3_Change()
Call HandleChoice(3)
End Sub
Private Sub HandleChoice(i As Long)
Dim n As Long
For n = i + 1 To maxOptions
shTarget.OLEObjects("Choice" & n).Object.Clear
Next n
If shTarget.OLEObjects("Choice" & i).Object.ListIndex > -1 Then
shTarget.OLEObjects("Choice" & i + 1).Object.List = Split(FillList(1), ",")
End If
End Sub