List to select up to 6 selected responses

E

Ellen

Is it possible to create a list in excel 2003 and allow up to 6 items on the
list to be selected and have them displayed in the cell?
 
E

Ellen

I looked at data validation. From what I could see, using Allow list I was
only able to select 1 item and not several.

Ellen
 
D

Dave Peterson

Sorry.

I thought that the the list could contain up to 6 items and that you'd select
one of those six.

You'll need a macro.

If that's ok...

Maybe you could use a listbox from the Forms Toolbar and a button from that same
Forms toolbar.

I put a listbox from the forms toolbar on a worksheet and put a button from the
forms toolbar right near it.

I right clicked on the listbox and chose format control.

Then on the Control tab, I assigned an input range (from another worksheet in my
test) and chose Multi in the selection type area.

Then I added a button from the Forms toolbar.

And I added this code to a General module in the workbook's project.

Option Explicit
Sub testme()
Dim iCtr As Long
Dim myLB As ListBox
Dim myStr As String
Dim myCell As Range

Set myLB = ActiveSheet.ListBoxes("List box 1")

Set myCell = ActiveSheet.Range("a1") 'Activecell '????

With myLB
For iCtr = 1 To .ListCount
If .Selected(iCtr) Then
myStr = myStr & vbLf & .List(iCtr)
End If
Next iCtr
End With

If myStr <> "" Then
myStr = Mid(myStr, 2)
End If

myCell.Value = myStr

End Sub

And then back to excel.
I rightclicked on the button and assigned this macro to that button.

I wasn't sure what you really wanted--did you want all the selected items in a
single cell?

That's what the code does. The selected items are separated by alt-enters.

And I put that string in A1 of the activesheet.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Top