PDA

View Full Version : Transfer Listbox Rowsource to Worksheet Range



LOSS1574
01-16-2009, 11:31 AM
How do I Transfer a Listbox Rowsource to Worksheet Range?

Let's say I have a userform with 1 list box and an up & down button. I adjust the data in Listbox 1 using the up/down button. I want to save the data back to the worksheet.

Also, the listbox is a multicolumn and has four (4) columns of data.

nst1107
01-16-2009, 12:25 PM
Here's an example how to do that. Download this file and run the userform.

LOSS1574
01-19-2009, 08:08 PM
How do i modify the code to add 2 listboxes of data to the same active worksheet range..

Step 1) Add data from listbox 1 to the active worksheet
Step 2) Add data from listbox 2 to last blank row (end of the data added from listbox 1) in the active worksheet
Step 3) clear both listboxes

Thank you...

nst1107
01-19-2009, 09:45 PM
This is how:

LOSS1574
01-20-2009, 02:16 PM
Once again thanks for your help and assistance.

I'm having trouble with my code (see below). When the sheet has pre-exisiting data in the worksheet. The data from both listboxes is being entered into the second row in lieu of the first row. I've tried to clear the ranges on the sheet and then place the data to no avail. Please advise
Sheet1.Activate
With Sheet1
Range("A1:F5000").Clear
End With
Dim i As Integer, j As Integer
With Sheet1
For i = 0 To ListBox2.ListCount - 1
For j = 0 To ListBox2.ColumnCount - 1
.Cells(i + 1, j + 1) = ListBox2.List(i, j)
Next
Next
For i = 0 To ListBox3.ListCount - 1
For j = 0 To ListBox3.ColumnCount - 1
.Cells(i + ListBox2.ListCount, j + 1) = ListBox3.List(i, j)
Next
Next
End With
ListBox2.Clear
ListBox3.Clear

Kind Regards

nst1107
01-20-2009, 02:36 PM
Not sure what the problem is without looking at your workbook. The code you've posted looks good to me, and it runs fine when I test it. Is the first item in your listboxes a blank row?

Kenneth Hobs
01-20-2009, 03:01 PM
As Nate said, it looks ok. UsedRange might be a better choice.

Another method:
Private Sub CommandButton1_Click()
Worksheets("Sheet1").UsedRange.Clear
AddArrayToColAOnSheet ListBox1.List, "Sheet1"
AddArrayToColAOnSheet ListBox2.List, "Sheet1"
End Sub

Private Sub AddArrayToColAOnSheet(anArray, sheetName As String)
Dim r As Range
With Worksheets(sheetName)
Set r = .Range("A" & Rows.Count).End(xlUp).Offset(1)
If IsEmpty(.Range("A1")) Then Set r = .Range("A1")
r.Resize(UBound(anArray, 2), UBound(anArray, 1)).Value = anArray
End With
End Sub

LOSS1574
01-20-2009, 03:01 PM
I've tried a couple different ways and it seems the data is not finding the first blank row, instead it's replacing the last row of data..

Should i use a find last blank row of data piece of code?

I'll post a sample file in a few.

LOSS1574
01-20-2009, 03:27 PM
Please see the attached sample file..

The problem lies in Row 22 on sheet 1.

The row should read: 22, test22, env22, cas22, und22 however, the row has been deleted or something worse.

I'll look into your suggestion from the above post.

nst1107
01-20-2009, 03:36 PM
Here's the fix: .Cells(i + ListBox2.ListCount + 1, j + 1) = ListBox3.List(i, j)

Edit: Interestingly enough, if you fill the listboxes using the .List() method, as in my example, you do not want the + 1. But, if you use the .RowSource property to fill the listboxes, you need the + 1. I wonder why this is.