VBA - how do I define a list object

[rirus]

I created a list In A worksheet, ListBox3. I am able to populate the list with data from a specified range of cells however, when I click on a number in the list I keep getting an Object Required error...

Sub ListBox3_Change()
'
Dim strCaseID As String
strCaseID = ListBox3.Column(0, ListBox3.ListIndex)

End Sub

Do I need to declare ListBox3 as a list?

Thanks,

Rirus

 

[Jerry Sullivan]

Hi Rirus,

One way to access the selected ListBox item is...

Sub ListBox3_Change()
    Dim strCaseID As String
    With ListBox3
        strCaseID = .List(.ListIndex, 0)
    End With
End Sub

 

[rirus]

I am getting... Object required

 

[Jerry Sullivan]

I am getting... Object required

Hmmm... That should work.

Do you have this code in the Sheet Code in which your ActiveX ListBox resides?

Do you have any other ListBox3 code that might be triggering first with an error before reaching ListBox3_Change (like ListBox3_Click)?

 

[rirus]

The Listbox3 is in a worksheet. When I inserted the list it had two options to choose from... Listbox or an ActiveX Listbox, I inserted the latter. Because it is a list and not an activeX list it is looking for an object. I'll bet that is it as I also have an activeX Combobox in the worksheet that works great. I'll bet if I changed the list to an activeX list it work.

Oh well, I got frustrated with this and thought it easier to put the list into a form. That is working great. I even added a second column to the list.

Thanks for all your help on this... I appreciate the response.

rirus

 

[Jerry Sullivan]

Happy to hear that you were able to figure it out.

I typically use ActiveX when the object is placed in the Worksheet, and Form Objects in a UserForm.

Cheers!