Why null reference to ListBox?

[Henry Markov]

My UserForm has a ListBox control named lbox. In the click handler of
another control on the same form I call a subroutine that has lbox as one of
its parameters. I get a "Run-time error '13': Type mismatch" and when I go
to debug mode and float the cursor over "lbox" in the VBA code I see "lbox =
Null." However when I examine lbox in the "Locals" debug window it is well
defined. What don't I understand? How can break mode debugging give
different values for the same reference in different windows? Why did the
error occur?

HM

 

[Dave Peterson]

There are two different listboxes in excel. One if from the Forms toolbar and
is placed on a worksheet--or a dialog sheet.

The other is the activeX control listbox--from the control toolbox toolbar on a
worksheet - or on a userform.

And since they share the same ListBox name, you have to be more specific:

Sub CalledByClick(myListBox as msforms.listbox)
...
End sub

Sub lbox_change()
Call CalledByClick(mylistbox:=lbox)
or
call calledbyclick(lbox)
or
calledbyclick lbox
end sub

You've got to be specific with all those controls that share names. But it
doesn't hurt to be specific if if there isn't any ambiguity.

sub aaaa(myCBX as msforms.combobox)

 

[Henry Markov]

Dave,
According to your explanation I was specific. I said my control was on a
UserForm. Your explanation says that the ListBox on a UserForm is the
ActiveX variety. I'm just an occasional VBA programmer so I'm often
ignorant of all the obsfucation in MS development tools such as giving two
different objects used within the same application the same name. I get to
Excel VBA from a worksheet through Tools->Macro->Visual Basic Editor. I
create modules and user forms. I drag controls onto the user forms from the
little Toolbox.

Note: I don't know what your code is supposed to be telling me.

HM

 

[Dave Peterson]

Does your called procedure look like:

sub aaaa(mylbx as listbox)

if yes, then that "as listbox" isn't correct.

If no, then I don't understand enough to help.

Notice the difference with:
sub aaaa(mylbx as msforms.listbox)

That msforms makes it specific.

 

[Henry Markov]

Dave,
Thanks, the msforms.listbox variant works. I would like to understand
better. "msforms" seems to be a namespace. I haven't done a lot of
VBA/Excel programming but I have never needed to use a namespace qualifier
before. Given that I dragged a graphical representation of a control from a
toolbox and Excel told me that the type was "ListBox" where is the clue that
I need to declare it in subroutine formal parameters as an msforms.ListBox?
How would I know in the general case that a namespace qualifier is needed
and what its value is? When I use VBA context-sensitive help the help text
never says that this listbox is an msforms.ListBox.

HM

 

[Dave Peterson]

I'm not sure what NameSpace means here. But qualifying the object never hurts.

Next time you're in the VBE, hit F2 to see the Object browser.
Search for Listbox
Rightclick in the results window and turn on "show hidden members"

You'll see the MSForms version and the Excel version. (Maybe library =
namespace in your thinking???)

I'm not sure I've seen a definitive resource for this--but I haven't spent any
time looking, either.

Dave,
Thanks, the msforms.listbox variant works. I would like to understand
better. "msforms" seems to be a namespace. I haven't done a lot of
VBA/Excel programming but I have never needed to use a namespace qualifier
before. Given that I dragged a graphical representation of a control from a
toolbox and Excel told me that the type was "ListBox" where is the clue that
I need to declare it in subroutine formal parameters as an msforms.ListBox?
How would I know in the general case that a namespace qualifier is needed
and what its value is? When I use VBA context-sensitive help the help text
never says that this listbox is an msforms.ListBox.

HM