PDA

View Full Version : How to get information from a ListBox to an Excel Worksheet



Shiraj_Hoque
12-01-2010, 01:05 PM
Hi Newbie here,
I have a code that takes information from one ListBox to another. Now I need the selected information to go from the second listbox to a spreadsheet.
I can get the information on the spreadsheet but its not dynamic enough. I think I need to incorporate a COUNTA with the Offset but dont know how to in VBA.

can someone guide me in the right direction and offer notes to explain. I'm keen to learn so would be great.


Private Sub CommandButton3_Save_to_Site_Specific_Lookups_Click()
Dim C As Integer, R As Integer
Dim RefCell As Range
Set RefCell = Worksheets("KB Wide Lookup").Range("D2")

With UserForms(0)
For R = 0 To ListBox4_Site_Specific.ListCount - 1
For C = 0 To ListBox4_Site_Specific.ColumnCount - 1
RefCell.Offset(R, C).Value = ListBox4_Site_Specific.List(R, C)
Next C
Next R
End With

End Sub
EDIT: please make sure you wrap your code in code tags using the VBA button
by Simon Lloyd

Simon Lloyd
12-02-2010, 05:50 AM
Can you explain what you think this should look like for you if it was to work?
ListBox4_Site_Specific.List(R, C)

Shiraj_Hoque
12-02-2010, 07:04 AM
Basically, I have 2 ListBoxes. One on the Left side of the Userform, the other on the Right side.
I also have a command button that is labelled “add” and another command button that is labelled “remove”.

The first Listbox “Rowsource” is linked to a set range in a workbook worksheet. The user can make choices from Listbox 1 and by clicking on the “add” button, he can transfer those choices to Listbox 2. The aim is to then have the choices that have been transferred to List Box 2 to make their way onto a workbook by clicking on “Save” (this is where the code should lie) – so that they can be picked up by another Userform later on in the process.

ListBox4_Site_Specific.List(R, C)

I have an offset function that actually does transfer the second Userform contents to the worksheet but it isn’t dynamic. What I mean by that is - assume I have made 10 choices, then pressed save – those 10 choices appear on the worksheet (that’s fine that’s what I want), but, lets pretend I’ve made a mistake and I should only have had chosen 7 choices – I can re-choose the 7 choices and press save again. The first 7 choices on the worksheet change (which is fine, that’s what I want it to do), but choices 8,9 and 10 still remain in the worksheet – those are the ones that should have disappeared.

I assume, I haven’t put in a COUNTA formula as that’s what I would have done in normal excel; but being a novice in VBA, I haven’t learnt how to do that. Would really appreciate a solution.
thanks

Simon Lloyd
12-02-2010, 09:46 AM
Can you supply a sample workbook? (it can be dummy data but the type and structure must be the same)