Solved Drop down list in Excel with user input and autofill

Microsoft Excel 2003 (full product)
January 17, 2012 at 05:57:12
Specs: Windows XP
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...


See More: Drop down list in Excel with user input and autofill

Report •


✔ Best Answer
January 25, 2012 at 12:28:20
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.



#1
January 17, 2012 at 07:02:18
re: 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.

Are you saying that you have an infinite number of Drops Down to deal with?

Wow!

In any case, you can specify the specific columns for which you want the routine to run in various ways. You could also ignore the specific location of the change as long as you don't mind the Input Box appearing any time that "Other" is entered into a cell, whether it was chosen from a Drop Down or not.

Just eliminate the check for the Target.Address.

Private Sub Worksheet_Change(ByVal Target As Range)
'If Drop Down choice is Other, get value
'from user and place it in Target Cell
  If Target = "Other" Then
   otherVal = Application.InputBox("Enter Other Value")
    If otherVal = False Then Exit Sub
     Range(Target.Address).Value = otherVal
   End If
End Sub

As for your second question, yes, it's possible to "turn off" the drop downs and populate the cells with a hard coded value (e.g. No) but the code would need to know specifically which cells to modify.

You could either leave the Drop Downs in place and have No as the only choice so that the user could not change it via the Drop Down or you could turn the Drop Down "on or off" based on the value in the Target cell.

We would need more information (most importantly, which cells should be populated wth No) before we could offer any solutions.

P.S. Please click on the following line and read the instructions found via that link before you post any more code in this forum. Thanks.

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


Report •

#2
January 17, 2012 at 07:21:50
Hi,

Thank you so much, that worked perfectly! (Also, apologies for not posting code properly, I read the link and will be sure to follow that next time!)

As for the infinite number, I meant that I have an infinite number of entries (theoretically) as I am entering about 50 patients a week for x number of weeks. So for the cells that I need to turn off the drop down in, it will be the same problem. Basically, I need it for columns I2-X2 but x rows since each week I will be adding to the list, if that makes sense. I would be fine with there being another drop down with only the option of NS when No is selected and all the other exclusion options when Yes is selected.

Thank you again!


Report •

#3
January 17, 2012 at 19:41:10
This code will deal with the Other as before.

In addition, as an example, the code will monitor Column A. If there is a change to Yes, it will place "Choose" and a Drop Down in Column B, same row. If the change is to No, the Validation Drop Down is deleted and NS will be placed in the cell.

Private Sub Worksheet_Change(ByVal Target As Range)
'If Drop Down choice is Other, get value
'from user and place it in Target Cell
  If Target = "Other" Then
    otherVal = Application.InputBox("Enter Other Value")
'If otherVal = False Then Exit Sub
      Range(Target.Address).Value = otherVal
  End If
'If Column = A, Check for Yes Or No
   If Target.Column = 1 Then
'If Value is Yes, then add Validation in Column B
        If Target = "Yes" Then
          Range("B" & Target.Row) = "Choose"
            With Range("B" & Target.Row).Validation
               .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                     Operator:=xlBetween, Formula1:="=$E$1:$E$3"
               .IgnoreBlank = True
               .InCellDropdown = True
               .InputTitle = ""
               .ErrorTitle = ""
               .InputMessage = ""
               .ErrorMessage = ""
               .ShowInput = True
               .ShowError = True
            End With
        End If
'If Value is No, Delete Validation and place NS in cell
        If Target = "No" Then
           With Range("B" & Target.Row)
              .Validation.Delete
              .Value = "NS"
           End With
        End If
   End If
End Sub

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


Report •

Related Solutions

#4
January 18, 2012 at 00:38:52
Hi,

That looks and sounds good, however, can I change B to be columns I:X? Can I enter the range into the code or repeat the strings of code for each letter?

As I have multiple studies (and NS rules them out of all of them), it would be great if I could autofill all of them with NS if that's chosen in column A.

Thank you!


Report •

#5
January 18, 2012 at 06:53:11
Essentially the same as before, except that it loops through Columns I-X adding or deleting Validation as required.


Private Sub Worksheet_Change(ByVal Target As Range)
'If Drop Down choice is Other, get value
'from user and place it in Target Cell
  If Target = "Other" Then
    otherVal = Application.InputBox("Enter Other Value")
'If otherVal = False Then Exit Sub
      Range(Target.Address).Value = otherVal
  End If
'If Column = A, Check for Yes Or No
   If Target.Column = 1 Then
'If Value is Yes, then add Validation in Columns I-X
        If Target = "Yes" Then
         For colNum = 9 To 24
          Cells(Target.Row, colNum) = "Choose"
            With Cells(Target.Row, colNum).Validation
               .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                     Operator:=xlBetween, Formula1:="=$E$1:$E$3"
               .IgnoreBlank = True
               .InCellDropdown = True
               .InputTitle = ""
               .ErrorTitle = ""
               .InputMessage = ""
               .ErrorMessage = ""
               .ShowInput = True
               .ShowError = True
            End With
         Next
        End If
'If Value is No, Delete Validation and place NS in Columns I-X
        If Target = "No" Then
         For colNum = 9 To 24
           With Cells(Target.Row, colNum)
              .Validation.Delete
              .Value = "NS"
           End With
         Next
        End If
   End If
End Sub

By the way, you can probably delete the following lines, since they are the defaults for Validation lists. I left them in just in case you wanted to add Titles and Error messages.

       
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True

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


Report •

#6
January 18, 2012 at 07:03:14
Okay, one more problem unfortunately - each column (I-X) has a different drop down list, which I have on a seperate worksheet in the workbook and that I have already set up the drop down lists for. When I enter the code you gave me, the drop down lists for the cells appear as what is actually in cell E. I tried to change it to the range of other cells but that doesn't work.

Sorry to be such a pain...


Report •

#7
January 24, 2012 at 06:09:54
Hi,

Would it be possible to get an answer about my last part?

Thanks!


Report •

#8
January 24, 2012 at 06:56:03
The code I offered was just an example of how it could be done. Since I have no way of knowing where the data for the dropdowns comes from, I can't write code to address that specific issue.

re: "I tried to change it to the range of other cells but that doesn't work."

Again, since I don't know what change you made, I can't tell you why it didn't work.

In general terms, what you need is a way to set the drop down for each column, which means that this argument needs to be changed for each column:

Formula1:="=$E$1:$E$3"

There are multiple way to accomplish that, but I can't offer any specific code without knowing where the data for each column comes from. Hopefully, the data for each list is in some sort sequential order so the code can "increment" the location each time through the loop as opposed to having to use a separate block of code for each of the 17 columns.

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


Report •

#9
January 24, 2012 at 07:04:07
Hi,

The data is on a seperate worksheet in the same book. It does go in order so drop down for column H comes from column A on worksheet 2; drop down list from column I comes from column B on worksheet 2, etc.

Can I just repeat

Formula1:="=$E$1:$E$3"

For each drop down list?


Report •

#10
January 24, 2012 at 07:40:36
No.

The code loops through the columns (For colNum = 9 To 24) setting the validation on one column each time through loop. What you need to do is change the "=$E$1:$E$3" each time it loops.

Is the length of the validation list the same for each drop down? If so what is that length?

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


Report •

#11
January 24, 2012 at 07:51:13
No, the length of each drop-down varies from 2-15

Report •

#12
January 24, 2012 at 07:52:02
Do the ranges in Sheets2 have Names?

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


Report •

#13
January 24, 2012 at 08:03:59
Yes, they are named for the columns that they fill in the actual worksheet.

As an example, one question is Stroke and the drop down is 'Yes' or 'No'. To create the drop down, I highlighted those two boxes (Yes and No) and named that 'Stroke' to populate the drop down list (i.e. the formula I put in the drop down list is =STROKE). The sheet with the lists for the drop down is also called Lists (not sure if that's important)


Report •

#14
January 24, 2012 at 09:00:01
I'm confused.

My question about the Named ranges refers specifically to the lists on Sheet2 that populate the Column I - X drop downs on Sheet1.

Are those lists all Named ranges?

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


Report •

#15
January 24, 2012 at 09:12:16
yes, that's what I meant, they are named ranges

Report •

#16
January 24, 2012 at 09:55:53
Ok, try this:

If you are not familiar with the CHOOSE function, look it up in the Excel Help files so that you'll understand this suggestion.

Note: Do not confuse the use of the CHOOSE function with my use of the word "Choose" in the cells with the drop downs. That's just a coincidence. In fact, I'll change it in the new code to eliminate any confusion.

What you can try is this:

After the line that reads:

For colNum = 9 To 24

Insert this:

nxtList = WorksheetFunction.Choose(colNum - 8, "Name1", "Name2", "Name3", "etc.)

Where Name1, Name2, etc. are the Named ranges from Sheet2, starting with the name for the Column A range and moving right.

Then replace this:

Formula1:="=$E$1:$E$3"

with:

Formula1:="=" & nxtList

You should end up with something like this:

For colNum = 9 To 24
   nxtList = WorksheetFunction.Choose(colNum - 8, "Name1", "Name2", "Name3")
     Cells(Target.Row, colNum) = "Select One"
       With Cells(Target.Row, colNum).Validation
               .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                    Operator:=xlBetween, Formula1:="=" & nxtList

Since colNum will equal 9 through 24 as it loops, colNum - 8 will equal 1 through 17. The CHOOSE function will pick up each range name in order and use it as the Validation list for Columns 9 (I) through 24 (X).

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


Report •

#17
January 25, 2012 at 06:38:12
Hi,

Now the drop downs work but it doesn't auto-populate
for No with NS, like before...here is the
formula I have, maybe it's a typing/copying error?

Private Sub Worksheet_Change(ByVal Target As Range)
'If Drop Down choice is Other, get value
'from user and place it in Target Cell
  If Target = "Other" Then
    otherVal = Application.InputBox("Enter Other Value")
'If otherVal = False Then Exit Sub
      Range(Target.Address).Value = otherVal
  End If
'If Column = A, Check for Yes Or No
   If Target.Column = 1 Then
'If Value is Yes, then add Validation in Columns I-X
        If Target = "Yes" Then
         For colNum = 9 To 24
          nxtList = WorksheetFunction.Choose(colNum - 8, "Yes", "Diagnosis", "DIAS4", "STITCHII", "STROKE", "TARDIS", "ENOS", "SOS", "DARS", "CADISS", "VIST", "METB", "IRIS", "DNA", "BMET", "GENESIS", "Soma", "Hospital")
     Cells(Target.Row, colNum) = "Select One"
       With Cells(Target.Row, colNum).Validation
               .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                    Operator:=xlBetween, Formula1:="=" & nxtList
            End With
         Next
        End If
'If Value is No, Delete Validation and place NS in Columns I-X
        If Target = "No" Then
         For colNum = 9 To 24
           With Cells(Target.Row, colNum)
              .Validation.Delete
              .Value = "NS"
           End With
         Next
        End If
   End If
End Sub


Report •

#18
January 25, 2012 at 08:30:12
re: "here is the formula I have"

This is not a formula, it is a macro, also known as VBA code.

re: "it doesn't auto-populate for No with NS, like before"

It works for me. I just pasted the code directly from your post into a worksheet.

I entered No in A11. NS appeared in I11:X11. The same thing happens in whatever Column A Row I enter No into.

However, I see a problem with your modification that you didn't mention.

You want Drop Downs in Columns I:X. That's 16 Drop Downs. When I look at this line I see 18 entries for the CHOOSE function.

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

The last 2 entries will never be chosen because colNum - 8 will only count from 1 to 16 since the loop is based on colNum = 9 to 24. In other words, it is only going to loop 16 times, not 18.

Why do you have 18 Range Names for only 16 Drop Downs?

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


Report •

#19
January 25, 2012 at 09:39:13
You're right, there should be 17, not 16 (or 18, I included one list that doesn't exist). Below is the code corrected for number and the correct Target.column (the yes or no question which should cause columns 9-25 to autopopulate NS when the answer is no).

Now when I select yes, it tells me there is a problem and when I select 'debug' it highlights the following section:

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                    Operator:=xlBetween, Formula1:="=" & nxtList

Overall it does work as I can just choose not to debug and then keep going but I wonder why that happens?

Here is the entire code as I have entered it:

Private Sub Worksheet_Change(ByVal Target As Range)
'If Drop Down choice is Other, get value
'from user and place it in Target Cell
  If Target = "Other" Then
    otherVal = Application.InputBox("Enter Other Value")
'If otherVal = False Then Exit Sub
      Range(Target.Address).Value = otherVal
  End If
'If Column = H, Check for Yes Or No
   If Target.Column = 8 Then
'If Value is Yes, then add Validation in Columns I-Y
        If Target = "Yes" Then
         For colNum = 9 To 25
           nxtList = WorksheetFunction.Choose(colNum - 8, "Diagnosis", "DIAS4", "STITCHII", "STROKE", "TARDIS", "ENOS", "SOS", "DARS", "CADISS", "VIST", "METB", "IRIS", "DNA", "BMET", "GENESIS", "Soma", "Hospital")
     Cells(Target.Row, colNum) = "Select One"
       With Cells(Target.Row, colNum).Validation
               .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                    Operator:=xlBetween, Formula1:="=" & nxtList
            End With
         Next
        End If
'If Value is No, Delete Validation and place NS in Columns I-Y
        If Target = "No" Then
         For colNum = 9 To 25
           With Cells(Target.Row, colNum)
              .Validation.Delete
              .Value = "NS"
           End With
         Next
        End If
   End If
End Sub


As always, thank you!!!


Report •

#20
January 25, 2012 at 12:28:20
✔ 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.


Report •

#21
January 26, 2012 at 04:06:03
That worked perfectly, thank you so much for all of your help!!!

Also, sorry again for errors and not reading the VBA posting rules closely enough!


Report •


Ask Question