Transfer multiple columns items form listbox to range

  • Thread starter Thread starter Rolo
  • Start date Start date
R

Rolo

Hi everybody, thank you for your help.

I have a Userform with a Listbox inside. This list box has 8 MULTIPLE
COLUMNS & many rows (coming from a range).

The problem is I do not know how to transfer all & every one of the 8
items from a selected row in the listbox to a range in a sheet.

Can you help me???
 
If you want to lift a row/column of data from an array
use application.index (= application.worksheetfunction.index)
with either the row or column identifier set to 0


Example:

Create a userform with 1 combobox and 1 commandbutton:

Option Explicit


Private Sub CommandButton1_Click()
Dim myArr
With ComboBox1
If .ListIndex = -1 Then
Beep
Else
myArr = Application.Index(.List, .ListIndex + 1, 0)
Range("a1").Resize(1, 8) = myArr
Range("a3").Resize(8, 1) = Application.Transpose(myArr)
End If
End With
End Sub


Private Sub UserForm_Initialize()
Dim i, j, myArr
ReDim myArr(1 To 20, 1 To 8)
For i = 1 To 20
For j = 1 To 8
myArr(i, j) = "Record" & i & " Field" & j
Next
Next
ComboBox1.List = myArr
ComboBox1.ColumnCount = 8
End Sub




keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
For example, your listbox is called "ListBox1". You want to send the items in your listbox to a range in Worksheet(1), start from Range("A1"). Number of columns is 8. Number of rows is variable.

Use the following statement:

Worksheets(1).Range("A1").Resize(ListBox1.ListCount, 8).Value = ListBox1.List()



----- Rolo wrote: -----

Hi everybody, thank you for your help.

I have a Userform with a Listbox inside. This list box has 8 MULTIPLE
COLUMNS & many rows (coming from a range).

The problem is I do not know how to transfer all & every one of the 8
items from a selected row in the listbox to a range in a sheet.

Can you help me???
 
OP said:
The problem is I do not know how to transfer all & every one of the 8
items from a selected row in the listbox to a range in a sheet.
Click to expand...

Your solution attempts to put the the whole list on the worksheet
--
Regards,
Tom Ogilvy

Edwin Tam (MS MVP) said:
For example, your listbox is called "ListBox1". You want to send the items
Click to expand...
in your listbox to a range in Worksheet(1), start from Range("A1"). Number
of columns is 8. Number of rows is variable.
 
Back
Top