I have form that has a number of list boxes that use command buttons to
transfer items from one list box to another, I want to save the items to a
sheet with a command button. Can do this with a text box but not with a list
box. Can some one give an example?

Maybe you could use something like:

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

With Worksheets("sheet1")
Set DestCell = .Range("A1")
End With

With Me.ListBox1
DestCell.Resize(.ListCount, 1).ClearContents

For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
DestCell.Value = .List(iCtr)
Set DestCell = DestCell.Offset(1, 0)
End If
Next iCtr
End With
End Sub
Private Sub UserForm_Initialize()
With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
.AddItem "a"
.AddItem "b"
.AddItem "c"
.AddItem "d"
.AddItem "e"
End With
End Sub


gbpg wrote:

I have form that has a number of list boxes that use command buttons to
transfer items from one list box to another, I want to save the items to a
sheet with a command button. Can do this with a text box but not with a list
box. Can some one give an example?

--

Dave Peterson

Can the Add item be a range? In one list box I have 1200 employees that can
be possibly selected.

"Dave Peterson" wrote:

Maybe you could use something like:

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

With Worksheets("sheet1")
Set DestCell = .Range("A1")
End With

With Me.ListBox1
DestCell.Resize(.ListCount, 1).ClearContents

For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
DestCell.Value = .List(iCtr)
Set DestCell = DestCell.Offset(1, 0)
End If
Next iCtr
End With
End Sub
Private Sub UserForm_Initialize()
With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
.AddItem "a"
.AddItem "b"
.AddItem "c"
.AddItem "d"
.AddItem "e"
End With
End Sub


gbpg wrote:

I have form that has a number of list boxes that use command buttons to
transfer items from one list box to another, I want to save the items to a
sheet with a command button. Can do this with a text box but not with a list
box. Can some one give an example?

--

Dave Peterson

You can use .additem to add the value from cells in a worksheet.

But you have more choices, too. You could use the .rowsource property and just
tell the listbox where to get the info:

Option Explicit
Private Sub UserForm_Initialize()
Dim myRng As Range

With Worksheets("sheet1")
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
.RowSource = myRng.Address(external:=True)
.ColumnCount = myRng.Columns.Count
.ColumnHeads = True
End With
End Sub



gbpg wrote:

Can the Add item be a range? In one list box I have 1200 employees that can
be possibly selected.

"Dave Peterson" wrote:

Maybe you could use something like:

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

With Worksheets("sheet1")
Set DestCell = .Range("A1")
End With

With Me.ListBox1
DestCell.Resize(.ListCount, 1).ClearContents

For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
DestCell.Value = .List(iCtr)
Set DestCell = DestCell.Offset(1, 0)
End If
Next iCtr
End With
End Sub
Private Sub UserForm_Initialize()
With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
.AddItem "a"
.AddItem "b"
.AddItem "c"
.AddItem "d"
.AddItem "e"
End With
End Sub


gbpg wrote:

I have form that has a number of list boxes that use command buttons to
transfer items from one list box to another, I want to save the items to a
sheet with a command button. Can do this with a text box but not with a list
box. Can some one give an example?

--

Dave Peterson


--

Dave Peterson

I am sorry I actually meant the other way around - My list list does get the
names from a sheet. I want to save the info that goes from the listbox one to
listbox 2 (which works) to cells on a sheet.

Listbox one---Listbox two-----sheet cells by a range (instead of adding
all of the cells indivually). The idea is that the cells with the multiple
selection could be used elsewhere after the bookcloses.



"Dave Peterson" wrote:

You can use .additem to add the value from cells in a worksheet.

But you have more choices, too. You could use the .rowsource property and just
tell the listbox where to get the info:

Option Explicit
Private Sub UserForm_Initialize()
Dim myRng As Range

With Worksheets("sheet1")
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
.RowSource = myRng.Address(external:=True)
.ColumnCount = myRng.Columns.Count
.ColumnHeads = True
End With
End Sub



gbpg wrote:

Can the Add item be a range? In one list box I have 1200 employees that can
be possibly selected.

"Dave Peterson" wrote:

Maybe you could use something like:

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

With Worksheets("sheet1")
Set DestCell = .Range("A1")
End With

With Me.ListBox1
DestCell.Resize(.ListCount, 1).ClearContents

For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
DestCell.Value = .List(iCtr)
Set DestCell = DestCell.Offset(1, 0)
End If
Next iCtr
End With
End Sub
Private Sub UserForm_Initialize()
With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
.AddItem "a"
.AddItem "b"
.AddItem "c"
.AddItem "d"
.AddItem "e"
End With
End Sub


gbpg wrote:

I have form that has a number of list boxes that use command buttons to
transfer items from one list box to another, I want to save the items to a
sheet with a command button. Can do this with a text box but not with a list
box. Can some one give an example?

--

Dave Peterson


--

Dave Peterson

If you're using a listbox where you allow multiselections, then you're going to
have to loop through the selected items--like in that first suggestion.

If you just wanted to put the list into a worksheet range:

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

With Worksheets("sheet1")
Set DestCell = .Range("b2")
End With

With Me.ListBox1
DestCell.Resize(.ListCount, .ColumnCount).Value = .List
End With
End Sub

I

gbpg wrote:

I am sorry I actually meant the other way around - My list list does get the
names from a sheet. I want to save the info that goes from the listbox one to
listbox 2 (which works) to cells on a sheet.

Listbox one---Listbox two-----sheet cells by a range (instead of adding
all of the cells indivually). The idea is that the cells with the multiple
selection could be used elsewhere after the bookcloses.

"Dave Peterson" wrote:

You can use .additem to add the value from cells in a worksheet.

But you have more choices, too. You could use the .rowsource property and just
tell the listbox where to get the info:

Option Explicit
Private Sub UserForm_Initialize()
Dim myRng As Range

With Worksheets("sheet1")
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
.RowSource = myRng.Address(external:=True)
.ColumnCount = myRng.Columns.Count
.ColumnHeads = True
End With
End Sub



gbpg wrote:

Can the Add item be a range? In one list box I have 1200 employees that can
be possibly selected.

"Dave Peterson" wrote:

Maybe you could use something like:

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

With Worksheets("sheet1")
Set DestCell = .Range("A1")
End With

With Me.ListBox1
DestCell.Resize(.ListCount, 1).ClearContents

For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
DestCell.Value = .List(iCtr)
Set DestCell = DestCell.Offset(1, 0)
End If
Next iCtr
End With
End Sub
Private Sub UserForm_Initialize()
With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
.AddItem "a"
.AddItem "b"
.AddItem "c"
.AddItem "d"
.AddItem "e"
End With
End Sub


gbpg wrote:

I have form that has a number of list boxes that use command buttons to
transfer items from one list box to another, I want to save the items to a
sheet with a command button. Can do this with a text box but not with a list
box. Can some one give an example?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

thanks Dave, I will try and see if I can get that to work..
Cheers

"Dave Peterson" wrote:

If you're using a listbox where you allow multiselections, then you're going to
have to loop through the selected items--like in that first suggestion.

If you just wanted to put the list into a worksheet range:

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

With Worksheets("sheet1")
Set DestCell = .Range("b2")
End With

With Me.ListBox1
DestCell.Resize(.ListCount, .ColumnCount).Value = .List
End With
End Sub

I

gbpg wrote:

I am sorry I actually meant the other way around - My list list does get the
names from a sheet. I want to save the info that goes from the listbox one to
listbox 2 (which works) to cells on a sheet.

Listbox one---Listbox two-----sheet cells by a range (instead of adding
all of the cells indivually). The idea is that the cells with the multiple
selection could be used elsewhere after the bookcloses.

"Dave Peterson" wrote:

You can use .additem to add the value from cells in a worksheet.

But you have more choices, too. You could use the .rowsource property and just
tell the listbox where to get the info:

Option Explicit
Private Sub UserForm_Initialize()
Dim myRng As Range

With Worksheets("sheet1")
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
.RowSource = myRng.Address(external:=True)
.ColumnCount = myRng.Columns.Count
.ColumnHeads = True
End With
End Sub



gbpg wrote:

Can the Add item be a range? In one list box I have 1200 employees that can
be possibly selected.

"Dave Peterson" wrote:

Maybe you could use something like:

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

With Worksheets("sheet1")
Set DestCell = .Range("A1")
End With

With Me.ListBox1
DestCell.Resize(.ListCount, 1).ClearContents

For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
DestCell.Value = .List(iCtr)
Set DestCell = DestCell.Offset(1, 0)
End If
Next iCtr
End With
End Sub
Private Sub UserForm_Initialize()
With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
.AddItem "a"
.AddItem "b"
.AddItem "c"
.AddItem "d"
.AddItem "e"
End With
End Sub


gbpg wrote:

I have form that has a number of list boxes that use command buttons to
transfer items from one list box to another, I want to save the items to a
sheet with a command button. Can do this with a text box but not with a list
box. Can some one give an example?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

thanks Dave, I got it to work and I think I can actually use this again,
although a generalist can be as dangerous as helpful to himself. I am on to
more now in my mad scheme....

Happy Easter.

"gbpg" wrote:

thanks Dave, I will try and see if I can get that to work..
Cheers

"Dave Peterson" wrote:

If you're using a listbox where you allow multiselections, then you're going to
have to loop through the selected items--like in that first suggestion.

If you just wanted to put the list into a worksheet range:

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

With Worksheets("sheet1")
Set DestCell = .Range("b2")
End With

With Me.ListBox1
DestCell.Resize(.ListCount, .ColumnCount).Value = .List
End With
End Sub

I

gbpg wrote:

I am sorry I actually meant the other way around - My list list does get the
names from a sheet. I want to save the info that goes from the listbox one to
listbox 2 (which works) to cells on a sheet.

Listbox one---Listbox two-----sheet cells by a range (instead of adding
all of the cells indivually). The idea is that the cells with the multiple
selection could be used elsewhere after the bookcloses.

"Dave Peterson" wrote:

You can use .additem to add the value from cells in a worksheet.

But you have more choices, too. You could use the .rowsource property and just
tell the listbox where to get the info:

Option Explicit
Private Sub UserForm_Initialize()
Dim myRng As Range

With Worksheets("sheet1")
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
.RowSource = myRng.Address(external:=True)
.ColumnCount = myRng.Columns.Count
.ColumnHeads = True
End With
End Sub



gbpg wrote:

Can the Add item be a range? In one list box I have 1200 employees that can
be possibly selected.

"Dave Peterson" wrote:

Maybe you could use something like:

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

With Worksheets("sheet1")
Set DestCell = .Range("A1")
End With

With Me.ListBox1
DestCell.Resize(.ListCount, 1).ClearContents

For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
DestCell.Value = .List(iCtr)
Set DestCell = DestCell.Offset(1, 0)
End If
Next iCtr
End With
End Sub
Private Sub UserForm_Initialize()
With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
.AddItem "a"
.AddItem "b"
.AddItem "c"
.AddItem "d"
.AddItem "e"
End With
End Sub


gbpg wrote:

I have form that has a number of list boxes that use command buttons to
transfer items from one list box to another, I want to save the items to a
sheet with a command button. Can do this with a text box but not with a list
box. Can some one give an example?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson