Several values in one cell. is it possible?

E

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
 
D

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
 
Top