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
------------------------------------------------
~~ Message posted from
http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/
~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements