Several values in one cell. is it possible?

[EliBY]

Hi all,

I want to be able to have a drop down list where I can select on
_or_more_ items to assign them to a single cell. Is this possible?

Thanks

 

[JulieD]

Hi

yes, Debra Dalgleish has examples on her website .. i'm not sure exactly
what page their on, but if you go to the index page and look through the
Data Validation topics you'll find it
www.contextures.com/tiptech.html

Cheers
JulieD

 

[Dave Peterson]

You may want to think about using a listbox and a button to copy the values to
the cell.

I used a listbox from the Forms toolbar and a button also from the Forms toolbar
on a worksheet and did this:

I rightclicked on the listbox and selected "format control". From the control
tab, I clicked on the "input range" box and pointed at a range in a worksheet
(a1:a10 for me). And I selected "multi" as the selection type.

Then I put a button right next to that listbox.

I put this code in a general module and assigned it to the listbox:

Option Explicit
Sub testme()

Dim myCell As Range
Dim iCtr As Long
Dim myStr As String

Set myCell = Worksheets("Sheet1").Range("c1")
myStr = ""
With ActiveSheet.ListBoxes("list box 1")
For iCtr = 1 To .ListCount
If .Selected(iCtr) = True Then
myStr = myStr & vbLf & .List(iCtr)
.Selected(iCtr) = False 'reset it??
End If
Next iCtr
End With

If myStr <> "" Then
myCell.Value = Mid(myStr, 2)
Else
MsgBox "please pick some values!"
End If

End Sub

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