Return activex combobox name

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Return activex combobox name

Post by YasserKhalil »

Hello everyone
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
But I have to use the name to return the name :)

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Return activex combobox name

Post by HansV »

You'll have to hard-code the name in the code.
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Return activex combobox name

Post by YasserKhalil »

Thanks a lot
Is there a way to abbreviate such codes
The codes are in worksheet module

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

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Return activex combobox name

Post by HansV »

I haven't tested this:

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
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Return activex combobox name

Post by YasserKhalil »

Amazing. Thank you very much. I justed changed the value 1 to the variable i in this part `Split(FillList(i), ",")`

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Return activex combobox name

Post by HansV »

Sorry, forgot about that.
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Return activex combobox name

Post by YasserKhalil »

Never mind at all my tutor. You have helped me a lot.
Thank you very much.
Is it possible to build a class for the ActiveX comboboxes on worksheet?

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Return activex combobox name

Post by HansV »

Could you attach a small sample workbook?
Best wishes,
Hans