Putting Listbox data in cells

[Todd Huttenstine]

I have a listbox that populates values from a specified
range on a worksheet. I have the MultiSelect property set
to fmMultiSelectMulti. This causes a check mark to be
placed to the left of each value in the dropdown box of
the Listbox. There can be a possible 26 values in the
listbox.

I also have a range Z1:Z26.

I would like for when the user puts a check in the check
box, the code put that value that is checked in the
corresponding cell in the range. For example, lets say
the user puts a check next "Todd" and a check next
to "Dan". I would like for Z1 to say "Todd" and for Z2 to
say "Dan".

How would I do this?

Todd

 

[Chip Pearson]

Todd,

Try something like the following:

Dim Rng As Range
Dim Ndx As Long
Set Rng = Range("Z1")
With Me.ListBox1
For Ndx = 0 To .ListCount - 1
If .Selected(Ndx) = True Then
Rng.Value = .List(Ndx)
Set Rng = Rng(2, 1)
End If
Next Ndx
End With


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Todd Huttenstine]

Tahnk you, worked.

Now is there a way to when I pull up the userform with the
listbox, that it can look in that range and if it finds a
value that matches one of the list box values, it can put
a check next to it automatically in the listbox. For
instance, lets say I open the form and put a check mark
next to "Todd", then I close the form and later on reopen
it, I would like to see the check mark already next
to "Todd" in the listbox.

Also lets say later on I open the form with the listbox
and try to check more values in it. If I do that now, it
just overwrites whats currently in the range. I want it
to be that when I check the checkboxes and then click ok
button, that the code not over overwrite values that are
in the range.


Thanx