Changing the Name of an Excel ActiveX ListBox

Question

I have a set of ActiveX controls and subs that fit together like this:

User types into an ActiveX TextBox,
TextBox_Click triggers execution of 3 subs.
The first sub updates the value of a named range on a sheet; based on that updated value a table is created in excel using an offset function
The second sub copies the table range updated in 3 and paste.values into a range on the sheet (to eliminate formulas)

Here's where it is breaking down.

When I set the sheet up I create one ListBox (ListBox1). The third of the three subs mentioned in #2 above tests to see if ActiveX 'ListBox1' exists in the sheet. If not, the intention is the code will determine what the ListBox is called (LB1 or LB2 or LB2 etc), changes the name to 'ListBox1', and pastes the table from #4 above into ListBox1. Then a ListBox1_Click command could trigger a unique set of code to execute.

Right now I'm just testing switching between the names ListBox1 and ListBox2.

The idea is that as a user types into TextBox1 for example, ListBox1 updates at every keystroke. So type "g" and the list shows 20 names starting with the first "g" in the database sheet. Then type "ge" and the list shows 20 names starting with the first "ge" in the database sheet etc etc. The user clicks on a name and ListBox1_click does something unique. If the user was typing in TextBox2 they would see different data in the ListBox (which I would like to rename as ListBox2) and if they click a unique set of code is executed by ListBox2_Click.

The problem in #5 is the I get error 'Object doesn't support this property or method' the FIRST time I try to execute. The second time it is ok. If I do something else on the sheet and come back to it i get the error again.

Here is the code for #5 which is in a Module:

Sub PutListInListBox()

    Dim OBJ As Object

    On Error Resume Next
        Set OBJ = ActiveSheet.OLEObjects("ListBox1")
    On Error GoTo 0

    If OBJ Is Nothing Then
        ActiveSheet.ListBox2.Name = "ListBox1"
    End If

    ActiveSheet.ListBox1.Clear
    ActiveSheet.ListBox1.List = Sheets("Search Criteria Control").Range("G1:G21").Value

End Sub

I have no idea what I'm doing wrong and any help is appreciated.

I hope I'm making this clear.

UPATED CODE I have a bunch of ActiveX Lables and Text boxes so I used the first form you suggested to search for specific name Cases. I've been reading about it and can't see what I have wrong here. After typing in TB5, the LB doesn't update. I click back to Design Mode and can see that the name is still ListBox2.

Any ideas?

Private Sub TextBox5_Change()

    Call UpdateValues(TextBox5.Value)
    Call CopyTable

    Dim OLEOBJ As OLEObject

    For Each OLEOBJ In ActiveSheet.OLEObjects
        Select Case OLEOBJ.Name
            Case "ListBox1", "ListBox2", "ListBox3"
                OLEOBJ.Name = "ListBox1"
                OLEOBJ.ListFillRange = Sheets("Search Criteria Control").Range("G1:G21").Address(external:=True)
        End Select
        Exit For
    Next

End Sub

Sure, added above. Let me know if there is more you would like to see. Thanks for taking in interest in this. — mchac
Note, I'm just working with two TBs and two LBs while I test how it could work. — mchac

L42 L42 · Accepted Answer · 2015-01-26T22:29:49

I'll concentrate on your Code#5.

First, if you are changing the name of the ListBox Object, it is better to iterate the Object Collection it belongs to since you are not sure of it's name. Something like this:

Dim oleobj As OLEObject
Dim sh As Worksheet: Set sh = ActiveSheet

For Each oleobj In sh.OLEObjects
    Select Case oleobj.Name
    Case "LB1", "LB2", "ListBox1", "ListBox2" 'put the possible names here
        oleobj.Name = "ListBox1" 'change it to the name you want
    End Select
    Exit For 'if you have more than 1 ListBox
Next

If you just want to change the name of an existing ListBox, then skip the checking.

For Each oleobj In sh.OLEObjects
    oleobj.Name = "ListBox1"
    Exit For 'if you have more than 1 ListBox
Next

Now, to assign values or list to it, you can directly assign the source Range, using ListFillRange Property after you've change its name.

oleobj.ListFillRange = Sheets("Search Criteria Control") _
    .Range("G1:G21").Address(, , , True) 'add this line within the loop

Take note that you need not clear the previous list. It will automatically update.
Since I'm not sure what you want to achieve entirely, I'll stop here. HTH though.

Changing the Name of an Excel ActiveX ListBox

1 Answers