VBA ListBox variable/object type

Joined
Nov 27, 2019
Messages
18
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
Hi all,

I have a UserForm with a ComboBox and a ListBox on it; their respective names are "ComboBox_Field" and "ListBox_Value". Their list values are currently strings, but might be dates or integers as well.

Now, I have a function to count the number of selected items in a ListBox:
VBA Code:
Function ListBoxSelectionCount(LB As ListBox) As Long
    Dim x As Long, count As Long

    For x = 0 To (LB.ListCount - 1)
        If LB.Selected(x) Then count = count + 1
    Next x

    ListBoxSelectionCount = count
End Function

For a reason unknown to me, I can't pass my ListBox to ListBoxSelectionCount() due to variable type mismatch:
Code:
Debug.Print VarType(ListBox_Value) ' returns 1 as for vbNull data type

However, if I redefine the function and pass the argument as Variant instead of ListBox
VBA Code:
Function ListBoxSelectionCount(LB As Variant) As Long
' exactly the same code as above
End Function
, everything works fine.

My question is: Why is the result of VarType(ListBox_Value) equal to vbNull? (I would expect this to be identical to my Combobox properties - I have VarType(ComboBox_Field)=8 as the ComboBox is populated with strings.)

Thanks in advance,
Dmitry
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Hi,
try making change to the parameter as shown & see if this resolves your issue

Rich (BB code):
Function ListBoxSelectionCount(ByVal LB As MSForms.ListBox) As Long
    Dim x As Long, count As Long

    For x = 0 To (LB.ListCount - 1)
        If LB.Selected(x) Then count = count + 1
    Next x

    ListBoxSelectionCount = count
End Function

Dave
 
Upvote 0
Thanks Dave, pretty impressive. It works perfectly. But... why? :) Why is it not the same as with ComboBoxes?

VBA Code:
Debug.Print TypeOf ComboBox_Field Is ComboBox            ' True
Debug.Print TypeOf ComboBox_Field Is MSForms.ComboBox    ' True
Debug.Print TypeOf ListBox_Value Is ListBox              ' False
Debug.Print TypeOf ListBox_Value Is MSForms.ListBox      ' True
 
Upvote 0
The Excel library has a Listbox object and it takes precedence. (It doesn't have a combobox object as it calls that a Dropdown)
 
Upvote 0
The Excel library has a Listbox object and it takes precedence. (It doesn't have a combobox object as it calls that a Dropdown)
Thank you Rory, it's clear. (However it's completely new to me that there exists two sets of object names with mismatches; I'll need to read a bit about the matter.)

The initial issue is resolved completely, thanks a lot guys!
 
Upvote 0
Different object libraries can have objects with the same name. For example, both Word and Excel have Range objects, so if you're automating Word from Excel (or vice versa) you should always explicitly declare variables as either Word.Range or Excel.Range.
 
Upvote 0
Alternatively, you could declare ListBox_Value as ListBox2 (Hidden member) which is unique to the MSForms Library.

VBA Code:
Debug.Print TypeOf ListBox_Value Is ListBox2               ' TRUE
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top