I have a userform with listbox set to multi select which gets list from a
worksheet. I want to be able to select a selection from the list and that
selection to go into another worksheet. Please can anyone help with this?

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

Steve wrote:

I have a userform with listbox set to multi select which gets list from a
worksheet. I want to be able to select a selection from the list and that
selection to go into another worksheet. Please can anyone help with this?

--

Dave Peterson

Thanks for your reply. I have copied the code into the userform but now I'm
getting an error message "runtime error 70", "access denied" when I try and
open userform.

"Dave Peterson" wrote:

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

Steve wrote:

I have a userform with listbox set to multi select which gets list from a
worksheet. I want to be able to select a selection from the list and that
selection to go into another worksheet. Please can anyone help with this?

--

Dave Peterson

Maybe you should share the code that is causing the trouble--and indicate the
offending line.

And share how you're populating the listbox. If you're using code (.additem),
then make sure the .rowsource is not used (me.listbox1.rowsource = "").

Steve wrote:

Thanks for your reply. I have copied the code into the userform but now I'm
getting an error message "runtime error 70", "access denied" when I try and
open userform.

"Dave Peterson" wrote:

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

Steve wrote:

I have a userform with listbox set to multi select which gets list from a
worksheet. I want to be able to select a selection from the list and that
selection to go into another worksheet. Please can anyone help with this?

--

Dave Peterson


--

Dave Peterson

In sheet one I have a command button, (CommandButton1), which opens
UserForm1. The code is:

Private Sub CommandButton1_Click()
UserForm1.Show
End Sub

On UserForm1 I have a List Box, (ListBox9), Which in properties is set for
MultiSelect. ControlSource is 'Sheet4'!A1:A15. Sheet4 A1:A15 has a list of
items.

After inserting your code then using contol button to open user form the
piece of code it stops at is the UserForm1.Show.

Thanks once again for your assistance, hope that's enough information.

"Dave Peterson" wrote:

Maybe you should share the code that is causing the trouble--and indicate the
offending line.

And share how you're populating the listbox. If you're using code (.additem),
then make sure the .rowsource is not used (me.listbox1.rowsource = "").

Steve wrote:

Thanks for your reply. I have copied the code into the userform but now I'm
getting an error message "runtime error 70", "access denied" when I try and
open userform.

"Dave Peterson" wrote:

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

Steve wrote:

I have a userform with listbox set to multi select which gets list from a
worksheet. I want to be able to select a selection from the list and that
selection to go into another worksheet. Please can anyone help with this?

--

Dave Peterson


--

Dave Peterson

You didn't share the code and you didn't indicate the line that caused the
problem.

I'm guessing that the problem is in the userform_Initialize procedure.

Steve wrote:

In sheet one I have a command button, (CommandButton1), which opens
UserForm1. The code is:

Private Sub CommandButton1_Click()
UserForm1.Show
End Sub

On UserForm1 I have a List Box, (ListBox9), Which in properties is set for
MultiSelect. ControlSource is 'Sheet4'!A1:A15. Sheet4 A1:A15 has a list of
items.

After inserting your code then using contol button to open user form the
piece of code it stops at is the UserForm1.Show.

Thanks once again for your assistance, hope that's enough information.

"Dave Peterson" wrote:

Maybe you should share the code that is causing the trouble--and indicate the
offending line.

And share how you're populating the listbox. If you're using code (.additem),
then make sure the .rowsource is not used (me.listbox1.rowsource = "").

Steve wrote:

Thanks for your reply. I have copied the code into the userform but now I'm
getting an error message "runtime error 70", "access denied" when I try and
open userform.

"Dave Peterson" wrote:

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

Steve wrote:

I have a userform with listbox set to multi select which gets list from a
worksheet. I want to be able to select a selection from the list and that
selection to go into another worksheet. Please can anyone help with this?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson