Multi select Listbox

[Steve]

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]

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]

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]

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]

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]

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.

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.

 

[Harald Staff]

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]

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!).

 

[Steve]

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.