Excel VBA - Assign object variable to userform Listbox

[Harry Flashman]

I have a userform which contains a ListBox called lbPivotFields_Selected.

How do I assign this to an object variable of the ListBox type (as opposed to just a generic Object)?

I tried this

Dim lb As ListBox
Set lb = lbPivotFields_Selected

But I get a Type Mismatch error message.

Can anyone see where I am going wrong, please. I would rather use the ListBox type because then I get intellisense. Or is this not possible?
Any assistance would be greatly appreciated.

 

[Norie]

Declare lb as an MSForms.ListBox.

Dim lb As MSForms.ListBox

 

[Harry Flashman]

Thanks Norie. It's weird because I don't have to specify MSForms for a ListBox on a worksheet. But your solution works. cheers.

 

[GTO]

I have a userform which contains a ListBox called lbPivotFields_Selected...

...Can anyone see where I am going wrong, please. I would rather use the ListBox type because then I get intellisense. Or is this not possible?
Any assistance would be greatly appreciated.

Greetings Harry,

Try specifying the ListBox as belonging to MSForms.

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>  <br><SPAN style="color:#00007F">Private</SPAN> lb <SPAN style="color:#00007F">As</SPAN> msforms.ListBox<br>  <br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> UserForm_Initialize()<br>  <br>  <SPAN style="color:#00007F">Set</SPAN> lb = lbPivotFields_Selected<br>  <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

If you look in the Object Browser (and choose Show Hidden Members), you will see that there are two list boxes, one belonging to Excel (which I would bet means the listbox for a Dialog Sheet) and one from MSForms.

Anyways and just curious... Why not just use lbPivotFields_Selected?

Hope that helps,

Mark

 

[Harry Flashman]

Greetings Harry,

Try specifying the ListBox as belonging to MSForms.

Option Explicit

Private lb As msforms.ListBox

Private Sub UserForm_Initialize()

Set lb = lbPivotFields_Selected

End Sub

If you look in the Object Browser (and choose Show Hidden Members), you will see that there are two list boxes, one belonging to Excel (which I would bet means the listbox for a Dialog Sheet) and one from MSForms.

Anyways and just curious... Why not just use lbPivotFields_Selected?

Hope that helps,

Mark

Thanks Mark. In answer to your question, simply to save typing when referring to the object more than a couple of times. In this case I probably could have achieved the same saving using With .... End With, but it so happens this time I used an object variable instead and got this error message, revealing something that I didn't know (ie the difference between an MSForms ListBox and an Excel ListBox).