Hi,

Can anyone help me. I am quite new to VBA and need some help taking
data from a list box that has multiple entires and inserting the entire
list into a range of cells.

I can tie one cell to the list box but not the entire list to a range

Thanks so much

Is this a listbox on a userform?

Or is this a listbox on a worksheet? If it's on a worksheet, did you use the
listbox from the Forms toolbar or from the Control Toolbox toolbar?

Mulberry wrote:

Hi,

Can anyone help me. I am quite new to VBA and need some help taking
data from a list box that has multiple entires and inserting the entire
list into a range of cells.

I can tie one cell to the list box but not the entire list to a range

Thanks so much

--

Dave Peterson

Its a list box from the Control Toolbox on a worksheet.


Dave Peterson wrote:
Is this a listbox on a userform?

Or is this a listbox on a worksheet? If it's on a worksheet, did you use the
listbox from the Forms toolbar or from the Control Toolbox toolbar?

Mulberry wrote:

Hi,

Can anyone help me. I am quite new to VBA and need some help taking
data from a list box that has multiple entires and inserting the entire
list into a range of cells.

I can tie one cell to the list box but not the entire list to a range

Thanks so much

--

Dave Peterson

I put a commandbutton from that same toolbar on the worksheet. And I used this
code:

Option Explicit
Private Sub CommandButton1_Click()
Dim iCtr As Long
Dim DestCell As Range

Set DestCell = Me.Range("a1")

With Me.ListBox1
DestCell.Resize(.ListCount, 1).ClearContents
For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) = True Then
DestCell.Value = .List(iCtr)
Set DestCell = DestCell.Offset(1, 0)
End If
Next iCtr
End With
End Sub
'create some data
Private Sub Worksheet_Activate()
Dim iCtr As Long
With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
For iCtr = 0 To 9
.AddItem "A" & iCtr
Next iCtr
End With
End Sub

Mulberry wrote:

Its a list box from the Control Toolbox on a worksheet.

Dave Peterson wrote:
Is this a listbox on a userform?

Or is this a listbox on a worksheet? If it's on a worksheet, did you use the
listbox from the Forms toolbar or from the Control Toolbox toolbar?

Mulberry wrote:

Hi,

Can anyone help me. I am quite new to VBA and need some help taking
data from a list box that has multiple entires and inserting the entire
list into a range of cells.

I can tie one cell to the list box but not the entire list to a range

Thanks so much

--

Dave Peterson

--

Dave Peterson

Thanks for this, appreciate the help. Only thing is that I used this
code with referencing the applicable Range and ListBox but it does not
seem to be doing anything. Any suggestions?
Dave Peterson wrote:
I put a commandbutton from that same toolbar on the worksheet. And I used this
code:

Option Explicit
Private Sub CommandButton1_Click()
Dim iCtr As Long
Dim DestCell As Range

Set DestCell = Me.Range("a1")

With Me.ListBox1
DestCell.Resize(.ListCount, 1).ClearContents
For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) = True Then
DestCell.Value = .List(iCtr)
Set DestCell = DestCell.Offset(1, 0)
End If
Next iCtr
End With
End Sub
'create some data
Private Sub Worksheet_Activate()
Dim iCtr As Long
With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
For iCtr = 0 To 9
.AddItem "A" & iCtr
Next iCtr
End With
End Sub

Mulberry wrote:

Its a list box from the Control Toolbox on a worksheet.

Dave Peterson wrote:
Is this a listbox on a userform?

Or is this a listbox on a worksheet? If it's on a worksheet, did you use the
listbox from the Forms toolbar or from the Control Toolbox toolbar?

Mulberry wrote:

Hi,

Can anyone help me. I am quite new to VBA and need some help taking
data from a list box that has multiple entires and inserting the entire
list into a range of cells.

I can tie one cell to the list box but not the entire list to a range

Thanks so much

--

Dave Peterson

--

Dave Peterson

The code that did the work was assigned to commandbutton1. Did you add a
commandbutton from the control toolbox toolbar to the worksheet?

And did you click on that button to do the work?

Mulberry wrote:

Thanks for this, appreciate the help. Only thing is that I used this
code with referencing the applicable Range and ListBox but it does not
seem to be doing anything. Any suggestions?
Dave Peterson wrote:
I put a commandbutton from that same toolbar on the worksheet. And I used this
code:

Option Explicit
Private Sub CommandButton1_Click()
Dim iCtr As Long
Dim DestCell As Range

Set DestCell = Me.Range("a1")

With Me.ListBox1
DestCell.Resize(.ListCount, 1).ClearContents
For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) = True Then
DestCell.Value = .List(iCtr)
Set DestCell = DestCell.Offset(1, 0)
End If
Next iCtr
End With
End Sub
'create some data
Private Sub Worksheet_Activate()
Dim iCtr As Long
With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
For iCtr = 0 To 9
.AddItem "A" & iCtr
Next iCtr
End With
End Sub

Mulberry wrote:

Its a list box from the Control Toolbox on a worksheet.

Dave Peterson wrote:
Is this a listbox on a userform?

Or is this a listbox on a worksheet? If it's on a worksheet, did you use the
listbox from the Forms toolbar or from the Control Toolbox toolbar?

Mulberry wrote:

Hi,

Can anyone help me. I am quite new to VBA and need some help taking
data from a list box that has multiple entires and inserting the entire
list into a range of cells.

I can tie one cell to the list box but not the entire list to a range

Thanks so much

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson