Multi select Listbox
 

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?

Multi select Listbox
 

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

Multi select Listbox
 

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

Multi select Listbox
 

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

Multi select Listbox
 

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

Multi select Listbox
 

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

Multi select Listbox
 

Hi Dave

Did too, the man says "it stops at is the UserForm1.Show". The debugger
doesn't track further into the userform_Initialize to display the real error
in those cases, which is a pain, especially at times when _initialize calls
subroutines in other modules. I know you know <g

Best wishes Harald

"Dave Peterson" wrote in message
...
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

Multi select Listbox
 

Oops. Thanks Harald. I expected to see more code--and didn't read (stupid
eyes) the whole message.

To the OP:

Open your workbook and go into the VBE.

Then go into the Userform_Initialize procedure and hit F8 (to step through your
code) or hit F5 to run (until it stops).

Then post that procedure and indicate the line causing the error (if you still
trust me!).



Harald Staff wrote:

Hi Dave

Did too, the man says "it stops at is the UserForm1.Show". The debugger
doesn't track further into the userform_Initialize to display the real error
in those cases, which is a pain, especially at times when _initialize calls
subroutines in other modules. I know you know <g

Best wishes Harald

"Dave Peterson" wrote in message
...
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

--

Dave Peterson

Multi select Listbox
 

Thanks Guys, got it working now.

Had to take out the linked cells from the propperties settings as that was
causing the conflict.

Works excelent, just what I wanted, thanks again.

"Dave Peterson" wrote:

Oops. Thanks Harald. I expected to see more code--and didn't read (stupid
eyes) the whole message.

To the OP:

Open your workbook and go into the VBE.

Then go into the Userform_Initialize procedure and hit F8 (to step through your
code) or hit F5 to run (until it stops).

Then post that procedure and indicate the line causing the error (if you still
trust me!).



Harald Staff wrote:

Hi Dave

Did too, the man says "it stops at is the UserForm1.Show". The debugger
doesn't track further into the userform_Initialize to display the real error
in those cases, which is a pain, especially at times when _initialize calls
subroutines in other modules. I know you know <g

Best wishes Harald

"Dave Peterson" wrote in message
...
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

--

Dave Peterson