Userform data to Excel sheet.

[James Batley]

I posted this query(see bottom) earlier and got this reply
(see below) but I don't really understand it. This is
probably because my queriy was not very clear.

My Userform has many ListBoxes each with Labels, I want
to take some of these ListBoxes and put the contents into
a spreadsheet that already has column headings. The sheet
headings and the labels are the same.

I don't know how to select the appropriote ListBoxes and I
can't figure out how to paste them into Excel.

I can open up a sheet and put in the headings.

Can someone help? Even if they just explain the code
already supplied?

Many thanks

James


Dim bk as Workbook
workbooks.Add
set bk = ActiveWorkbook

i = 0
for each ctrl in Userform1.Controls
if typeof ctrl is MSforms.ListBox
i = i + 1
With bk.Worksheets(1)
.cells(2,i).Resize(ctrl.Listcount,1).Value = ctrl.list
end if
Next

' not sure where the headings would come from

fName = Application.GetSaveAsFileName()

bk.SaveAs fName
bk.Close SaveChanges:=False

might be a start.

in message
news:[email protected]...

 

[Tom Ogilvy]

Well there was no talk of pre labelled columns in the original question or
of labels either.

In fact, the stated requirement was:

1. open a new workbook

A new workbook wouldn't have pre labeled columns and such as you now
describe. Anyway,

how do we know which label goes with which listbox.

You said some of these listboxes - how would we know which ones.

the code shown does what you originally asked.

If opens a new workbook, then put the list of each listbox (assume single
column listboxes) in a column of the 1st sheet of the new workbook. Then
allows the user to save the workbook.