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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform and reading multiple listboxes
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform and reading multiple listboxes
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) "Bijl167" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform and reading multiple listboxes
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 ----------------------------------------------- ~~ 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 |
Reply |
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 |