Creating a drop down list, where the user can more than 1 answer

[Willow1221]

I know how to create a drop down list, however, I am trying to create a
tracking worksheet where users can select as many responses that apply.

Any ideas?

 

[Gary''s Student]

See DV0017 in:

http://www.contextures.on.ca/excelfiles.html#DataVal

 

[Willow1221]

Thank you. This does show me what can be done, however, I am unable to locate
instructions for how to apply this. Any guidance?

 

[L. Howard Kittle]

Here is the code from DV0017, paste it in a sheet module. Look for line
wrap which I believe I see in the "iCol=Cells..." line. Make sure it is all
on one line.

Put your data Validation cell (or cells) in column C. This complies with
the "If Target.Column = 3 Then..." code OR change the "3" in the code to
the column you want and install your data validation('s) in that column.
(A=1, B=2 etc.)

Now as you select from the Data Validation the choices will be listed to the
right of the Validation cell in that row.

In the example shown in DV0017 the yellow cells all have Data Validation in
them and you can choose a yellow cell and select various choices in the
respective drop-downs and see how they become listed to the right of each
cell.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo exitHandler

Dim rngDV As Range
Dim iCol As Integer

If Target.Count > 1 Then GoTo exitHandler

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
Application.EnableEvents = False
If Target.Column = 3 Then
If Target.Value = "" Then GoTo exitHandler
If Target.Validation.Value = True Then
iCol = Cells(Target.Row, Columns.Count).End(xlToLeft).Column + 1
Cells(Target.Row, iCol).Value = Target.Value
Else
MsgBox "Invalid entry"
Target.Activate
End If
End If
End If

exitHandler:
Application.EnableEvents = True

End Sub

HTH
Regards,
Howard

 

[JB]

http://boisgontierjacques.free.fr/pages_site/listes_cascade.htm#ChoixSuccessifs

JB
http://boisgontierjacques.free.fr

 

[L. Howard Kittle]

Hi Gary"s Student,

I tried to adjust this line of code to list the selections vertical instead
of horizontal and was unable to come up with the correct syntax. I
bonked...!

iCol = Cells(Target.Row, Columns.Count).End(xlToLeft).Column + 1

Regards,
Howard