Userform and reading multiple listboxes

[Bijl167]

Hi all,

I've got a userform with 8 different listboxes. All items in the
different listboxes are loaded by an userform_initialize sub.
Under the ok button I want to have a piece of code that put's all
selected items in a list on a sheet. The listboxes multiselection
property is true

for reading out a multiselection list box I use:
---------------------------------------------------------------------------------
Public Function FillArray() As String
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim myarr() As String
ReDim myarr(ListBox1.ListCount - 1)
k = 1
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
myarr(j) = ListBox1.List(i)
Sheets("Zone Table format").Cells((k), "A") = myarr(j)
j = j + 1
k = k + 1
End If
Next i
ReDim Preserve myarr(j)
FillArray = myarr(j)
End Function
-----------------------------------------------------------------------------

This works with a normal list box in Excel. However I do not seem to
get it working in combination with a userform. I keep on getting back
the error message: "no object", so Excel seems not to recognize the 8
list boxes.

I've initialized my userform in the following manner (shows only a
part):
---------------------------------------------------------------------------------
Private Sub UserForm_Initialize()

With EU1list
..AddItem "Europe 1"
..AddItem "Belgium"
..AddItem "France North"
..AddItem "France Rest"
..AddItem "Germany"
..AddItem "Italy"
..AddItem "Luxembourg"
..AddItem "Netherlands"
..AddItem "United Kingdom"
..MultiSelect = fmMultiSelectExtended
End With


With NAlist
..AddItem "North America"
..AddItem "Canada"
..AddItem "United States"
..MultiSelect = fmMultiSelectExtended
End With

With LAlist
..AddItem "Latin America"
..AddItem "Argentina"
..AddItem "Brazil"
..AddItem "Chile"
..AddItem "Mexico"
..MultiSelect = fmMultiSelectExtended
End With

'etc etc

End With

End sub
----------------------------------------------------------------------------------


Does any one know how I can let the listboxes to be recognised by VBA?
So what should I put in the privat sub of the CmdOK button to get my
selected items in one single column?

I really hope someone can solve this
Many thanks in advance

cheers
maarten

 

[Jan Karel Pieterse]

Hi Bijl167,

Does any one know how I can let the listboxes to be recognised by VBA?

Precede them with the userform's name:

With Userform1.Lisbox1
...
End With

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[Bob Phillips]

Maarten,

Have you tried preceding the reference to the listboxes by the userform name
, for example

If Userform1.ListBox1.Selected(i) = True Then

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Hi all,

I've got a userform with 8 different listboxes. All items in the
different listboxes are loaded by an userform_initialize sub.
Under the ok button I want to have a piece of code that put's all
selected items in a list on a sheet. The listboxes multiselection
property is true

for reading out a multiselection list box I use:
-------------------------------------------------------------------------- -------
Public Function FillArray() As String
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim myarr() As String
ReDim myarr(ListBox1.ListCount - 1)
k = 1
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
myarr(j) = ListBox1.List(i)
Sheets("Zone Table format").Cells((k), "A") = myarr(j)
j = j + 1
k = k + 1
End If
Next i
ReDim Preserve myarr(j)
FillArray = myarr(j)
End Function
-------------------------------------------------------------------------- ---

This works with a normal list box in Excel. However I do not seem to
get it working in combination with a userform. I keep on getting back
the error message: "no object", so Excel seems not to recognize the 8
list boxes.

I've initialized my userform in the following manner (shows only a
part):
-------------------------------------------------------------------------- -------
Private Sub UserForm_Initialize()

With EU1list
AddItem "Europe 1"
AddItem "Belgium"
AddItem "France North"
AddItem "France Rest"
AddItem "Germany"
AddItem "Italy"
AddItem "Luxembourg"
AddItem "Netherlands"
AddItem "United Kingdom"
MultiSelect = fmMultiSelectExtended
End With


With NAlist
AddItem "North America"
AddItem "Canada"
AddItem "United States"
MultiSelect = fmMultiSelectExtended
End With

With LAlist
AddItem "Latin America"
AddItem "Argentina"
AddItem "Brazil"
AddItem "Chile"
AddItem "Mexico"
MultiSelect = fmMultiSelectExtended
End With

'etc etc

End With

End sub
-------------------------------------------------------------------------- --------


Does any one know how I can let the listboxes to be recognised by VBA?
So what should I put in the privat sub of the CmdOK button to get my
selected items in one single column?

I really hope someone can solve this
Many thanks in advance

cheers
maarten

creating financial statements

 

[Bijl167]

I've tried:

Private Sub CmdOK_Click()
With FrmZoneSheet.EU1list
FillArray
End With

Unload Me
End Sub

this does not work. The error message still shows "Object required"

I've also tried:

Private Sub CmdOK_Click()

With FrmZoneSheet.EU1list

Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim myarr() As String
ReDim myarr(FrmZoneSheet.Eulist.ListCount - 1)
k = 1

For i = 0 To FrmZoneSheet.Eulist.ListCount - 1

If FrmZoneSheet.Eulist.Selected(i) = True Then

myarr(j) = FrmZoneSheet.Eulist.List(i)
Sheets("Zone Table format").Cells((k), "A") = myarr(j)
j = j + 1
k = k + 1

End If
Next i
ReDim Preserve myarr(j)
End With

Unload Me
End Sub

his generates the error message: "method or data member not found


Any other suggestions