PDA

View Full Version : UserForm w/2 listboxes



chem101
02-21-2011, 09:42 AM
Hello Everyone,

I have a worksheet where I would like to use a UserForm with 2 listboxes. Listbox1a one would be populated by data located in a worksheet called WidgetTable. The user would select an item from listbox1a and click on an Add Item button to move that item to listbox2. There are 3 columns of data on the worksheet called WidgetTable. What I would like to have happen is when the user clicks on Ok on the userform the selected item in listbox2 goes to another worksheet in the workbook called GetWidgetData. All three columns of data are shown in listbox one but only column one is shown in listbox two after the selection is made. Upon clicking Ok on the userform I would like all three columns for the selected item in listbox2 to be copied to GetWidgetData. I can get this to work if you manually click on the item selected in listbox2 after its been selected. Naturally I don't want to have to do this manually. Also I would like to limit the selections in listbox2 to one selection. I have attached a copy of the workbook.
Any help you can provide will be greatly appreciated.
Thank you!!

IBihy
02-21-2011, 10:25 AM
Hello chem101,

one small step at a time:
In the properties of the list box ListBox1a, you need set the property "MultiSelect" to "1 - fmMultiSelectMulti" so the user can add more than one item at a time. You can do this in the designer.
Currently, your ListBox2 is limited to a one item selection by "MultiSelect" property, which is "0 - fmMultiSelectSingle". So this box should be the way you want it.

Do I get you right with this: you would like to copy all three columns to GetWidgetData?
Just an idea, no guarantee: You can address each column in ListBox1a. The List property of the list box object has two parameters, The list item, e.g. 3, and an optional parameter for the column. Attention: Numbering starts with 0!!!! You might address this such:
varSelectedRow = ListBox1a.List(lngRowCt, lngColCt)


HTH,
Isabella

chem101
02-21-2011, 01:23 PM
Thank you for your time. I have a few questions. I would like to be able to copy the info from listbox 2 into wks GetWidgetData. I would like to be able to copy the data row by row depending on the selection in listbox2. Can this be done?

Thank you.

IBihy
02-21-2011, 02:45 PM
Hello chem101,

oh yes, you can do that.
For the correct understanding's sake:
Previously, you said, ListBox2 is supposed to be a single select box only. Now it's not? Puzzled.
Rudimentarily, copy selected content from the list to a worksheet is done such.
BTW, my Excel2003 fails on thís line in "AddButton_Click

' This may work if ListBox1a is single select mode
ListBox2.AddItem ListBox1a.Value

I would cycle through the selected items in ListBox1a and add them to ListBox2 item by item.
You already do that, cycling through Listbox1a, fairly at the top of the module.

chem101
02-21-2011, 08:03 PM
Thank you for your time and info. I got listbox2 to transfer the data to the worksheet GetWidgetData using this code:

Private Sub OKButton_Click()
Dim rngFind As Range
Dim strFirstAddress As String
With wksWidgetTable.UsedRange
Set rngFind = .Find(ListBox2.List(0, 0), LookIn:=xlValues)
If Not rngFind Is Nothing Then
strFirstAddress = rngFind.Address
Do
rngFind.EntireRow.Copy wksGetWidgetData.Range("A" & wksGetWidgetData.Rows.Count).End(xlUp).Offset(1, 0)
Set rngFind = .FindNext(rngFind)
Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstAddress
End If
End With

I have two questions/issues. Can the number of selections put in listbox2 be limited to one item only - from one selection from listbox1a? The second issue is that when I select Widget 1, and transfer it to GetWidgetData it also copies Widget 10 and Widget 11. How can this be prevented.
Thank you for your assistance!!!

chem101
02-21-2011, 08:14 PM
I also added to the code the following to try and correct the additional items from being copied:

Option Explicit
Option Compare Binary

This didn't work. Any help you can provide will be greatly appreciated!!
Thank you!!

chem101
02-22-2011, 06:37 AM
a