How can I identify each ListBox item to paste into cells?

[AmyJade]

Hi,

I'm in need of your help!!!

I'm trying to find a way so that I can identify listbox items on a userform so that when the user clicks on a command button to transfer these items in the listbox into another worksheet and fills the cells in let's say, range A1 (fill down), that each individual item has been copied and pasted with the exact data from the listbox.

Any thoughts?

AmyJade

 

[bmoremu]

I'm assuming the user is allowed to select multiple items in the listbox? In that case, do something like:

For i = 0 To 9
If ListBox1.Selected(i) = True Then
Cells(i, 1) = ListBox1.List(i)
End If
Next i

".Selected" is an array automatically made by the listbox. You might want a row counter for the worksheet so that the values are placed one after the other.

 

[AmyJade]

My listbox is not a multi select listbox. Is that my problem? I have my userform set up so that you can pick the selected items from one listbox and "Add" or "Remove" the item to the second listbox.

 

[bmoremu]

You want a multiselect listbox if you want to allow the user to add/remove multiple items. You said items instead of item so I'm guessin you want multiselect.

 

[AmyJade]

Another problem with this:

Is there any way that the items that appears in the multiSelectListbox can hold separate information attached to each of the items selected? For example, if I selected two items (i.e. candy bars and soft drinks), a command button could open up a worksheet and bring the two items selected into specific cells with their own information (i.e. For candy bars--Aero, Caramilk, Crunch, Mr.Big and for soft drinks--Pepsi, Sprite, Crush) to be filled down in a specified range of cells? I don't want the information to be displayed in the listbox though (just the item name)

Any thoughts?

 

[bmoremu]

Well the additional info would have to be listed in some sort of table or in VB. Then you'd have to probably use some IF statements to say "If this item is selected....then return these values".

 

[AmyJade]

Thanks for the tip, but it's the coding that I'm having difficulty writing. I don't really know where to begin. I'm sorry, I'm new to VBA!

*The additional info is on a separate hidden worksheet.

 

[bmoremu]

S'alright, I've only been doing it a few months myself, experience is the best way to learn I think. The coding I had in mind was....under the command button click event...have it search through the selected items like I wrote above, i.e.:

For i = 0 To 9
    If ListBox1.Selected(i) = True Then
        ([i]Destination Cell[/i]).Value = (ListBox1.List(i)[i]'s corresponding values on the hidden sheet[/i])
    End If
Next i

Hope this helps.

 

[AmyJade]

Thanks again for the help.

I think the main problem I have is not the selection, but having the items selected in the listbox appear in the correct range of cells in the worksheet with the additional info also copied to the cells.Since the additional info does not appear in the listbox but are held in a separate worksheet, how would I get vba to call up this separate worksheet (without actually showing the worksheet)with the selected range of cells to be copied into the next worksheet, with just a click on the command button on the userform? I don't know where to begin!!!

 

[bmoremu]

I see...in the VB editor, select the hidden worksheet with the values, and under its properties change its "(Name)", first property, not "Name" further down. Now in your code you can reference this sheet, i.e. TestName.Range("A1"), without showing the sheet. Besides forums like this, be sure to try the VB help, too, I've learned lots by just looking stuff up in there.