Data from listbox to Excel sheet

[James Batley]

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

 

[Tom Ogilvy]

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.