Data Validation Drop-Down Box

[techie99]

I have a named range on a sheet that is used to populate a data validation drop-down box. In my range I have blank cells so the user can easily add something to data validation drop-down without having to worry about increasing the size of the range so that data validation picks it up. The only problem I'm encountering with this is when the user clicks the data validation drop-down box it doesn't show the top of the drop-down list of values. The top choice when they initially click the drop-down is the first blank cells, so all they see is an empty drop-down box, which means they have to know to scroll up. I would like to see the very top of the drop-down when I click that drop-down arrow. I programmatically create the drop-downs as follows, is there any line of code I can add to this that will tell it to do what I want? In this example, "snow" is my named range.

With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=snow"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = False
.ShowError = True
End With

 

[PHV]

without having to worry about increasing the size of the range
Have a look here:

http://www.tek-tips.com/faqs.cfm?fid=1331

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[techie99]

Not sure about that. I would really like to work with what I got. Any other ideas?

 

[Gavona]

Have your users add to the bottom of the list. Then either have a named range that adjusts automatically as PHV suggests (best)
or use yourrange.CurrentRegion within the code that redefines the validation criteria each time something is added to the list (unnecessary)


Gavin