move Listbox item to specific area in worksheet

Posted by graphage on April 30, 2001 6:40 AM

I have created two list boxes. listbox1 is on the left and contains a large list. listbox2 is on the right. The users select data from listbox1 and it moves over to listbox2. How do I take the data from listbox2 and have it transfered to cell B4 of a worksheet??? Thanks for the help!!

Posted by Dave Hawley on April 30, 2001 6:56 AM

Hi Graphage

If your listbox is NOT set for multiselect you would simply use:

Sheet1.Range("B4") = ListBox1.Value

If it is set for Multiselect you would use:

For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
Sheet1.Range("B4") = ListBox1.List(i, 0) & Sheet1.Range("B4")
End If
Next i

This would place the selected items in the first Column (0) of the Listbox all into Range B4

Dave

OzGrid Business Applications

Posted by graphage on April 30, 2001 12:51 PM

Hey Dave...this code doesn't seem to do anything when I place it behind my 'OK' button. What could I be doing wrong???

Posted by Dave Hawley on April 30, 2001 1:39 PM

Graphage, which code did you use ?
If you used:
Sheet1.Range("B4") = ListBox1.Value

Then you cannot have your Listbox MultiSelect Property set to: 1-fmMultiSelectMulti
If it is, you need to use:

For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
Sheet1.Range("B4") = ListBox1.List(i, 0) & Sheet1.Range("B4")
End If
Next i

Dave
OzGrid Business Applications

Posted by graphage on April 30, 2001 2:04 PM

Hello Dave...I used the code for the multiselect option and it just doesn't seem to do a thing. I created a "Sheet1" and made sure all my names were spelled correctly and still no luck. When I click the button nothing happens??? What do you think it could be? Thanks a lot for the help!!!!!

Posted by graphage on April 30, 2001 2:11 PM

Just out of curiousity, to make sure we are on the same page...my original message was that I take data from listbox1 and move it to listbox2 via a button. I then want to take the items from listbox2 and have them transfered to my sheet. I noticed that in the code you used listbox1. I changed it to reflect listbox2, since that is where I wanted to take the data from to be placed in my sheet. Is that OK??? just checking! Thanks again!!

Posted by Dave Hawley on April 30, 2001 2:19 PM

Graphage, changing to Listbox2 is not a problem.
The code:
Sheet1
Is the CodeName of a Worsheet, not the Tab name. Try changing it to:
Sheets("sheet1")
or whatever your sheet is called

Are you sure ListBox2 is set for MultiSelect. The default is for single. If you still have no luck I could send you a working example.

Dave

OzGrid Business Applications

Posted by graphage on May 01, 2001 6:08 AM

Hey Dave...if you could send an example, that would be fantastic. Thanks so much for taking the time to be so helpful!!! Please send it to graphage@hotmail.com.

Thanks!!!