Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform and reading multiple listboxes
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reading Multiple Files | Excel Worksheet Functions | |||
Is there a way to create multiple listboxes in same collumn? | Excel Discussion (Misc queries) | |||
Multiple listboxes to update pivot table? | Excel Discussion (Misc queries) | |||
Reading fields from multiple new files | Excel Discussion (Misc queries) | |||
Userform reading a cell value | Excel Programming |