MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Drop Down Lists

  Asked By: Holly    Date: Jan 12    Category: MS Office    Views: 1111

I have a range of cells that I would like to put into a drop down
list. The range of cells changes, so the list needs to be dynamic
also. I know how to create a dynamic range for a validation drop down
list, my only problem is when I do it, the list will contain several
blank spaces. I have one sheet with a set 13 different dropdown lists,
one right below the others. On my next sheet, I want to have a
dropdown list based off of what was chosen on the previous page of 13
dropdown lists. So, the list could range from 1-13 items. When I try
to do a dropdown list, it will give me a list with whatever was
chosen, plus the blank spaces from the ones not chosen. Anyone have
any suggestions as to how to get this validation to work correctly.
The formula I used is:

=OFFSET('As is Spend'!$H$14,0,0,COUNTA('As is Spend'!$H:$H),1)
This formual has worked for me in the past for dynamic ranges, but
I've never drawn that list from previous drop down lists so it does
not work for me now.



3 Answers Found

Answer #1    Answered By: Yvette Griffin     Answered On: Jan 12

I suggest you do the following:

1. Loop through the range  of 13 cells.

2. Add to a new string (e.g. strFormula) the values of all cells  that
are not blank, separated by commas. At the end of loop, strFormula
will be equal to something like:

3. Assign strFormula to 'Formula1' property of 'Validation' object of
your target cell ($H$14), which will contain dropdown list  based on
what was chosen, excluding any blanks.

Answer #2    Answered By: Brent Brown     Answered On: Jan 12

I'm not that familiar with VB. I don't know how to do that.
Any examples of code?

Answer #3    Answered By: Arnelle Schmidt     Answered On: Jan 12

No problem. This code will do it. I assume that cell J14 will hold
your required validation  list. Put these code lines in the code
module of your worksheet (I used 'Sheet1'):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngA As Range, rngB As Range
Dim strFormula As String

' Exit if any cell other than J14 is selected
If Target.Address <> "$J$14" Then Exit Sub

' Identify the range  of 13 dropdown  lists, starting from cell H14
Set rngA = ThisWorkbook.Worksheets("Sheet1").Range("H14:H26")

' Loop through the 13 cells
For Each rngB In rngA
' Operate only on non-blank cells
If rngB <> "" Then
' Add the values of non-blank cells  in a single string, separated by
' strFormula will be something like
' (value1,value4,value6,value7,value10,value12,)
strFormula = strFormula & rngB & ","
End If
Next rngB
' Remove the last comma
strFormula = Left(strFormula, Len(strFormula) - 1)

' Change the validation list  of cell J14
With ThisWorkbook.Worksheets("Sheet1").Range("J14").Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:=strFormula
End With

End Sub

Read carefully through code comments. Check help for any keyword if

Didn't find what you were looking for? Find more on Drop Down Lists Or get search suggestion and latest updates.