Export date from listbox to Excel

Could anyone help me to export date from listbox to Excel

Please help me!

Is this a listbox in a userform in excel or a listbox in a form outside of excel?

Is this a listbox in a userform in excel or a listbox in a form outside of excel?


Listbox in a form outside of excel.
I need from within vb to export to excel some date which are in List1 and List2 bt never done something like this

Thanks in advance !

I'm assuming that you've never exported anything to Excel before.
So First thing. Add a reference to Microsoft's Excel Object Library.
Goto Projects -> References and choose Microsoft's Excel Object Library.

Enter the following code.

Dim xlApp As Excel.Application
Dim xlSh As Excel.Worksheet
Dim i As Long
Set xlApp = New Excel.Application

xlApp.Visible = True
xlApp.Workbooks.Add
Set xlSh = xlApp.Workbooks(1).Worksheets(1)
For i = 1 To List1.ListCount
xlSh.cells(i, 1).Value = List1.List(i - 1)
Next

Set xlSh = Nothing
Set xlApp = Nothing

I'm assuming that you've never exported anything to Excel before.
So First thing. Add a reference to Microsoft's Excel Object Library.
Goto Projects -> References and choose Microsoft's Excel Object Library.

Enter the following code.

Dim xlApp As Excel.Application
Dim xlSh As Excel.Worksheet
Dim i As Long
Set xlApp = New Excel.Application

xlApp.Visible = True
xlApp.Workbooks.Add
Set xlSh = xlApp.Workbooks(1).Worksheets(1)
For i = 1 To List1.ListCount
xlSh.cells(i, 1).Value = List1.List(i - 1)
Next

Set xlSh = Nothing
Set xlApp = Nothing


Tank you Wamphyri this code runs fine
But How could I integrate the second Listbox(List2) here

Tanks in advance

I have added the second list box Like bellow is it a righte decision?


Private Sub Command3_Click()
Dim xlApp As Excel.Application
Dim xlSh As Excel.Worksheet
Dim i As Long
Dim y As Long

Set xlApp = New Excel.Application

xlApp.Visible = True
xlApp.Workbooks.Add
Set xlSh = xlApp.Workbooks(1).Worksheets(1)

For i = 1 To List1.ListCount

xlSh.Cells(i, 1).Value = List1.List(i - 1)

Next

For y = 1 To List2.ListCount

xlSh.Cells(y, 2).Value = List2.List(y - 1)

Next

Set xlSh = Nothing
Set xlApp = Nothing

End Sub

Looks ok. The real test however is does it work the way you want it to?

Looks ok. The real test however is does it work the way you want it to?

Oh! There is no problem with this code!

I have a form that contains a combobox and a list box. i want the contents of the listbox to change depending on what is selected in the combobox. I would want the listbox to be inactive (which I dont know how to do yet) untill a selection was made in the combobox. then at this point depending on what choice was made in the combobox a particular list would load into the listbox. what is the best way to do this?



Thanks a lot for the help.

The listbox you can set to Enabled = False initially
As for the listbox changing based on the selection in the combobox you can use a Select Case statement in the Change event of the Combobox
A small example

Private Sub ComboBox1_Change()
ListBox1.Enabled = True
Select Case ComboBox1.Text
Case "Choice1"
ListBox1.Clear
ListBox1.AddItem "This"
ListBox1.AddItem "That"
Case "Choice2"
ListBox1.Clear
ListBox1.AddItem "Cat"
ListBox1.AddItem "Dog"
End Select
End Sub

thanks a lot for the help.


is there anyway to pass variabes, in the case arrays, along with the form. when i initialized i put a bunch of values from a sheet into arrays in order to put them in the listbox and combobox. i want to pass them from the initializing routine to the combobox_change routine


The listbox you can set to Enabled = False initially
As for the listbox changing based on the selection in the combobox you can use a Select Case statement in the Change event of the Combobox
A small example

Private Sub ComboBox1_Change()
ListBox1.Enabled = True
Select Case ComboBox1.Text
Case "Choice1"
ListBox1.Clear
ListBox1.AddItem "This"
ListBox1.AddItem "That"
Case "Choice2"
ListBox1.Clear
ListBox1.AddItem "Cat"
ListBox1.AddItem "Dog"
End Select
End Sub