Creating a drop down list with a multitude of entries selected

[run2live]

Hi,

Is it possible to create a drop down list in Excel where you could "check" a
multitude of entries from the list? When you would view the drop down list
you would see all the entries that have been selected. For example if I had
a drop down list showing when a certain task is being done (ie. daily,
weekly, monthly, annualy, etc.) the user would have the possibility to select
more than one option in the list and a check mark would appear next to it for
example. Your help is greatly appreciated.

 

[Dave Curtis]

Hi,
You need a macro to do this for you. Try the following code which will put
the contents of a dropdown list into the cell to the right. Change the
Target.Offset values to alter the position.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
If Target.Count > 1 Then GoTo exitHandler
Application.EnableEvents = False

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
If Target.Value = "" Then GoTo exitHandler
If Target.Offset(0, 1).Value = "" Then
Target.Offset(0, 1).Value = Chr(149) & Target.Value
Else
Target.Offset(0, 1).Value = _
Target.Offset(0, 1).Value _
& Chr(10) & Chr(149) & Target.Value
End If
End If

exitHandler:
Application.EnableEvents = True
End Sub

Dave