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

The problem it only copies one selection to once cell, I need the
entire list box copied to a range of cells. Can this be done?

Basically I have to listboxes at the moment and using and add/remove
command buttons to transfer the selected items into the second listbox.
Now I want to insert all the selected items into a range of cells.


Dave Peterson wrote:
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

Maybe this would help:

http://groups.google.co.uk/group/mic...a816dc 6f30b2

or

http://snipurl.com/10dw1

It doesn't use any worksheet cells at all. But the other suggestions will work
when/if you want to populate the worksheet.

Mulberry wrote:

The problem it only copies one selection to once cell, I need the
entire list box copied to a range of cells. Can this be done?

Basically I have to listboxes at the moment and using and add/remove
command buttons to transfer the selected items into the second listbox.
Now I want to insert all the selected items into a range of cells.

Dave Peterson wrote:
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

--

Dave Peterson