How to have multiple values in a single cell?

[yajiv.vijay]

We have data validation to limit user to select one item from list. Do
we have anything like a dropdown box with checkboxes to each items, so
that we can select multiple items? If yes, how to hanle them from vba?

 

[Dave Peterson]

You could use a listbox from the Control toolbox toolbar and a commandbutton
from that same control toolbox toolbar to extract the selected items.

Put a listbox and a commandbutton from that control toolbox toolbar on a
worksheet.

Put some test data in B1:B10 (item list).

The selected items will go in A1:A10.

Rightclick on the worksheet tab that holds these controls and select view code.

Paste this in:

Option Explicit
Private Sub CommandButton1_Click()

Dim DestCell As Range
Dim iCtr As Long

Set DestCell = Me.Range("a1")

With Me.ListBox1
'remove existing entries
DestCell.Resize(.ListCount, 1).ClearContents
For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) = True Then
DestCell.Value = .List(iCtr)
Set DestCell = DestCell.Offset(1, 0)
End If
Next iCtr
End With
End Sub
Private Sub Worksheet_Activate()
With Me.ListBox1
.List = Me.Range("b1:b10").Value
.ListStyle = fmListStyleOption
.MultiSelect = fmMultiSelectMulti
End With
End Sub


Select a different worksheet and then select this worksheet -- I used
worksheet_activate to populate the listbox. I didn't know how it should be
populated.

Then select a few items and click the button.

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