Unable to Refer to Listbox on Sheet

user_13763393 0
Viewed 679

I haven't used list boxes before and don't need to use objects too often in my work, but for some reason, my code runs OK for this:

ActiveSheet.ListBox1.AddItem MyName(i)

but if I try to get the listbox using a worksheet instead of the ActiveSheet, it errors

wkTest.ListBox1.AddItem MyName(i)

Where test is the worksheet on which ListBox1 lives. What is the best way to reference a listbox if you want to refer it to it based on the sheet it's? Or is this not the right way to go about doing this.

vba excel listbox
1 Answers

Shortcomings of the List Box ActiveX Control?

Setup

  • Open a new worksheet and in the Developers tab click on Insert and under ActiveX Controls click on List Box (ActiveX Control).
  • "Draw" the control onto the worksheet. Copy the following code into a standard module (e.g. Module1).

Conclusions

  • The first part of the code demonstrates that although you can access the ListBox by its name via the Shapes and OLEObjects collections, both do not provide a way to add items to the ListBox.
  • The only way to do this seems to be to use the CodeName, which is demonstrated through various solutions in the second part of the code.

This was inspired by the ideas from Rory and Michael Smith in the comments.

The Code

Option Explicit

Sub ListBoxTest()

    ' Worksheet
    Dim wkTest As Worksheet: Set wkTest = ThisWorkbook.Worksheets("Sheet1")

    ' Shape - no use
    Dim shTest As Shape: Set shTest = wkTest.Shapes("ListBox1")
    Debug.Print "Shape", shTest.Name, shTest.Left
    'shTest.AddItem "One" 'Run-time Error '438': Object doesn't support ...

    ' OLEObject - no use
    Dim ooTest As OLEObject: Set ooTest = wkTest.OLEObjects("ListBox1")
    Debug.Print "OLEObject", ooTest.Name, ooTest.Left
    'ooTest.AddItem "One" 'Run-time Error '438': Object doesn't support ...

    Dim obj As Object

    'Set obj = wkTest.ListBox1 ' Compile error: Method or data member not found.

    ' Object 1 Solution
    Set obj = Worksheets("Sheet1").ListBox1
    Debug.Print "Object 1", obj.Name, obj.Left
    obj.AddItem "One"

    ' Object 2 Solution
    Set obj = Sheet1.ListBox1
    Debug.Print "Object 2", obj.Name, obj.Left
    obj.AddItem "Two"

    ' Object 3 Solution
    wkTest.Activate
    Set obj = ActiveSheet.ListBox1
    Debug.Print "Object 3", obj.Name, obj.Left
    obj.AddItem "Three"

    ' Favorite
    With Sheet1.ListBox1
        Debug.Print "Favorite", .Name, .Left
        .AddItem "Four"
    End With

End Sub

Sub ListBoxClear()
    Dim obj As Object: Set obj = Worksheets("Sheet1").ListBox1
    obj.Clear
End Sub
user_98140690
Related