Which variable to represent a listbox?

[and]

I would like to use a procedure again and again for several listboxes in
a user form.

Therefore, I need to use a variable that can represent the various list
boxes.

Which variable type should I use?

=========================
Private Sub userform_initiate()
Dim oLBox As Object 'Object type DOES NOT WORK
End Sub
-------------------------
Private Sub butBrowse01_click() 'Browse button 01 next to list box 01
oLBox = ThisUserForm.ListBox_01
SelectFiles (False) 'AllowMultiSelect = false
End Sub
-------------------------
Private Sub SelectFiles(bMultSel As Boolean)

Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
Dim vrtSelectedItem As Variant
With fd
.AllowMultiSelect = bMultSel
If .Show = -1 Then
For Each vrtSelectedItem In .SelectedItems
oLBox.AddItem (vrtSelectedItem) '???
Next
Else
End If
End With
Set fd = Nothing

End Sub
=========================

ANDy

 

[Malcolm Smith]

Andy

There are a couple of things wrong with your code and your understanding
of things.

The first is that you have made a mistake with the SCOPE of the oLBox
variable. It is declared in the UserForm_Initiate() method and,
therefore, the variable is only visible in that that method.

If you declared it at the top of the form class code then the scope of the
variable would be visible throughout the class.

The second problem is tied in with the first. You say that the Object
type doesn't work. It does; it just that you declared it in the wrong
scope.

You should really have declared it as a ListBox type so that it's easier
to debug and to work with. Also it'll be easier to read your code when
you return to it in three months time.


Now, the last problem is when you associate the variable to the actual
listbox itself. You wrote:
oLBox = ThisUserForm.ListBox_01

This is not going to work. The oLBox variable is an Object (or a
ListBox, which is also an object, but a very specific one). What these
variables are are actually POINTERS. One doesn't actually hold the
ListBox in this variable (think about it; this is not actually possible)
but what these object variables do is to point to where the ListBox lies
in memory.

Therefore, one needs to use the keyword Set before this statement:

Set oLBox = ThisUserForm.ListBox_01

And if you placed the oLBox declaration at the top of the class then this
would work.

Does this work?
- Malc
www.dragondrop.com (retired!)

 

[Helmut Weber]

Hi Andy,

a maybe somewhat unusual way would be,
to create an array of listboxes.
Unlike VB, at design time, VBA does not propose
to create an array of controls,
when copying and pasting a control.
So, when designing the form and what is on it,
I create, lets say 6 listboxes,
ListBox1 to ListBox6, and assign, one by one,
each listbox to an entry in an array of listboxes.

Like this, but take care of the sequence, in which the listboxes
where created, or check for digits in addition in the controls' name.


' declarations
Dim arList(6) As ListBox
Dim oCtrl As Control
' ---
Private Sub UserForm_Activate()
For Each oCtrl In Me.Controls
If Left(oCtrl.Name, 7) = "ListBox" Then
i = i + 1
Set arList(i) = oCtrl
End If
Next
For i = 1 To 6
arList(i).AddItem "Entry1 " & "list " & CStr(i)
arList(i).AddItem "Entry2 " & "list " & CStr(i)
arList(i).AddItem "Entry3 " & "list " & CStr(i)
Next
End Sub
' ---
Private Sub CommandButton1_Click()
MsgBox arList(4).List(1)
End Sub

HTH

Greetings from Bavaria, Germany

Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98
http://word.mvps.org/

 

[and]

Hi Malcolm,

Thanks for your advice. It does work. I used the Listbox variable type,
as you suggested.

The funny thing is that when I hover my mouse pointer over the oLBox
variable after a value is assigned to it, no valus is visible in the
little yellow box.

Best regards,
ANDy
--

 

[and]

Hi Helmut,

I understand from Malcolm's reply that I made a scope + Set error. That
is solved now. I will certainly try your method also, since it's simply
great fun to experiment and play with VBA.

Have a nice day,
ANDy
--

 

[Malcolm Smith]

Andy

The reason why when you hover over the variable you see nothing (no, not
Nothing) is that it's a pointer and it's pointing to an object and if you
can tell me how to display the value of a whole object or component then
you're a better man than I, Gungerdin!

- Malc
www.dragondrop.com