Dave,

I did as you said here is waht happens, I click the macro button, I get a
run time error 1004 unable to get the listboxes property of the worksheet
class. If I click debug it points to Set myLB = .ListBoxes("List box 1")

Once I get this set up will my staff be able to select multiple selections
and that will when populate in the field box????

THanks so much for your help!
--
Thanks,,
Kim


"Dave Peterson" wrote:

It depends on where the data is and what kind of listbox you're going to use.

I'm gonna suggest that you put your list on another worksheet (say sheet2,
A1:A10).

Then show the Forms toolbar (view|toolbars)
select the listbox and put it where you want it.
Right on that listbox and choose Format Control
On the Control tab, change the input range to:
'sheet2'!a1:a10
(adjust to match your stuff)

Change the selection type to:
Multi

Now click on the button (also on that Forms toolbar)
dismiss that "assign macro" dialog--we'll do that later.
put a nice button right next to the listbox.
Change the caption to what you want:
Click to Continue
(or something nice)

Now hit alt-f11 to get to the VBE
hit ctrl-r to see the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste this in that window:

Option Explicit
Sub CopyTheSelection()

Dim iCtr As Long
Dim DestCell As Range
Dim myLB As ListBox

With ActiveSheet
Set myLB = .ListBoxes("List box 1")
Set DestCell = .Range("a1")
End With

With myLB
DestCell.Resize(.ListCount, 1).ClearContents
For iCtr = 1 To .ListCount
If .Selected(iCtr) Then
DestCell.Value = .List(iCtr)
Set DestCell = DestCell.Offset(1, 0)
End If
Next iCtr
End With

End Sub

Back to the excel window (alt-f11 again)
rightclick on that button and choose assign macro

Assign CopyTheSelection to this button.



Kim K wrote:

I created a word doc with drop a drop down list for my staff. One staffer
wants to be able to make multiple selections from the drop down list. Should
I recreate this in excel, and do I use a list or combox box option? i see
how to set the box to multi instead of single but then I can not figure out
how to get the data into the box.

Can someone give me good directions on how or point me to a tutorial on how
to do this?
--
Thanks,,
Kim

--

Dave Peterson