Allowing Multiple Possible Responses in Excel Spreadsheet?

[lyndy256]

Hi,

I have the following question I am trying to represent in Excel:

6. What type of resource extraction or economic development projects are occurring in this village tract?
1. Mining 2. Logging
3. Industrial estate or Special Economic Zone 4. Commercial agricultural plantation
5. Cement Factory 6. Large hydro-electric dams
7. Gas pipelines 8. Highway construction
9. Other (please specify)…………………………………

I have thought of two ways to go about this: a checkbox or allow the user to select multiple responses from a drop down list. The check box options seems a bit clunky and takes up a lot of space and I am not sure how to program the cell to allow the user to select multiple responses from one drop down.

I'd appreciate any guidance with this.

Regards,
Lyndy

[FDibbins]

Have you started putting a workbook together yet? We would like to see what you have, how far you are, what you are working with, a few samples of your expected outcome?

[lyndy256]

Hi,

I have created the form already, but am having trouble figuring out how to best represent two items: Number of Key Informants and question number 6, which asks about type of resource extraction in the assessed village tract.

In the attached Excel worksheet, I've had to delete several questions because file is too big to send over this exchange, but I left pertinent ones so you can get an idea where I am going. I've also attached paper version of the data entry form.

Many thanks for all your help!

Best,
Lyndy

[lyndy256]

Hi,

I figured it out with following code but I don't understand why macros don't work if the worksheet is protected. I want to protect the worksheet so user can't accidentally alter code or change drop down lists and field titles.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
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
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column = 11 Or Target.Column = 24 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal & ", " & newVal
End If
End If
End If
End If

exitHandler:
Application.EnableEvents = True

End Sub

[lyndy256]

I figured out the solution on my own to allow data entry clerk to enter multiple responses in one cell. The code is:

Please Login or Register  to view this content.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
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
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column = 11 Or Target.Column = 23 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal & ", " & newVal
End If
End If
End If
End If

exitHandler:
Application.EnableEvents = True

End Sub

Please Login or Register  to view this content.

Thanks,
Lyndy