Userform data to Excel sheet.
 

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.

"James Batley" wrote
in message
...
Hi,

I have designed a UserForm which allows the user to
select
specific items from a master spreadsheet. These items
with some attributes are then added to a series of
listboxes.
I would like to be able to use a command button and....

1. open a new workbook
2. put the listbox data into columns with headings
3. give the option to save the workbook with a name.

point 3 is only just on my wish list, it is the first two
points that are really causing me problems.

If anyone can help, it would be most appreciated.

Many thanks

James

Userform data to Excel sheet.
 

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.

--
Regards,
Tom Ogilvy



"James Batley" wrote in message
...
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.

"James Batley" wrote
in message
...
Hi,

I have designed a UserForm which allows the user to
select
specific items from a master spreadsheet. These items
with some attributes are then added to a series of
listboxes.
I would like to be able to use a command button and....

1. open a new workbook
2. put the listbox data into columns with headings
3. give the option to save the workbook with a name.

point 3 is only just on my wish list, it is the first two
points that are really causing me problems.

If anyone can help, it would be most appreciated.

Many thanks

James