Home/ Questions/Q 14955
Next
Answered
computing
  • 13
Asked: 2021-12-17T13:58:28+00:00 In:

Solved Drop Down List In Excel With User Input And Autofill

  • 13

Hi,

I apologize in advance if this is confusing and it may even not be possible but I do appreciate any help!

I am making a screening log for patients for clinical trials. I have made several drop down lists for the exclusion criteria for the studies using the validation option. I am missing two things, however.

1) One of the options from the drop down list is Other where I would like to have a text box pop up for the user to input a value. I’ve seen a code for this before:

Private Sub Worksheet_Change(ByVal Target As Range)
‘If Drop Down choice is Other, get value
‘from user and place it in A1
If Target.Address = “$A$1” Then
If Target = “Other” Then
otherVal = Application.InputBox(“Enter Other Value”)
If otherVal = False Then Exit Sub
Range(“A1”).Value = otherVal
End If
End If
End Sub

The problem is that my input range needs to be across various columns and infinite rows and I don’t know how to do that. (I apologize if this is basic stuff…)

2) We screen all patients but some don’t have the disease they need for trials – this would automatically exclude them. Would it be possible to have a column that asks if they have the disease (Yes or No) and then if No automatically populates the other columns as Non-disease but if Yes then allows for the drop down lists I have (which have the exclusion criteria)?

Thanks in advance for any help, I’ve been googling and trying things on my own but I don’t seem to be getting very far…

Share

    1 Answer

    1. Best Answer
      First. to make your code easier to read in both the VBA editor and in this forum, please use the “continuation character” (an underscore) to break up long lines of code. A space then underscore tells VBA that the next line is a continuation of the current line:

      nxtList = WorksheetFunction.Choose(colNum - 8, "Diagnosis", "DIAS4", _
          "STITCHII", "STROKE", "TARDIS", "ENOS", "SOS", "DARS", "CADISS", _
          "VIST", "METB", "IRIS", "DNA", "BMET", "GENESIS", "Soma", "Hospital")

      I can think of 2 reasons why the code might present an error:

      1 – One of the Names you have listed is not a Named Range and therefore the code can not use that Name as the source for the Drop Down.

      In this case the code will probably error out whenever you enter Yes in Column H, even if No was in the cell previously.

      2 – The Row already contains the Drop Downs and the code is trying to “Add” a Validation List where one already exists.

      In this case the code will probably only error out when there are already Drop Downs in Columns I:Y. Actually a Drop Down in any one of those columns would cause the error since the code loops through each column adding the validation one by one and will fail as soon as it finds the first cell with Validation already applied.

      If that’s the case, then add a .Delete just before the Validation is added:

      With Cells(Target.Row, colNum).Validation
               .Delete
               .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                    Operator:=xlBetween, Formula1:="=" & nxtList

      That will Delete the current validation and allow the code to Add validation back in.

      Click Here Before Posting Data or VBA Code —> How To Post Data or Code.

      • 0