Copy listbox items to excel table.

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Copy listbox items to excel table.

Post by adam »

Hi anyone,

The following code is working fine except it does not copy the Listbox data to the first row of the table. Instead, it copes to the second row of the table. What may be the reason for this?

Code: Select all

Private Sub cmdEnter_Click()

Dim ProjectsTable As ListObject
Dim LastRow As Range

Sheets("Projects Data").ListObjects("Projects").ListRows.Add

Set ProjectsTable = Sheets("Projects Data").ListObjects("Projects")
Set LastRow = ProjectsTable.ListRows(ProjectsTable.ListRows.Count).Range

LastRow.Resize(ListBox1.ListCount, ListBox1.ColumnCount).Value = ListBox1.List

End Sub
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Copy listbox items to excel table.

Post by HansV »

LastRow is (surprise, surprise) the last row of the table:

Code: Select all

Set LastRow = ProjectsTable.ListRows(ProjectsTable.ListRows.Count).Range
If you want to write to the first row, you should change that line to refer to the first row instead of to the last row (and perhaps change the name of the variable, to avoid confusion)
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Copy listbox items to excel table.

Post by adam »

Thanks for the help Hans. Really appreciate it.
Best Regards,
Adam