Excel VBA: Named range + string value as validation list?

Question

Alright, basically what I want to do is set a data validation list for a specific cell. This list should contain a certain range of cells I've specified PLUS a string value added to it. I have a dialog box that asks the user for a name, and then I need the list to display containing a set range of cells with "Other: " & Name added to it.

name = "Test"
With Worksheets("Tijdsregistratie").Cells(aangepasteRij, 4).Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=nameRange, name"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

Something like the above code (obviously the formula doesn't work). Is there any way to do this using a specific formula, or am I going to have to find another way to get this done?

This might be of some help: http://stackoverflow.com/questions/4783019/can-i-use-vba-function-to-return-a-dynamic-list-of-acceptable-values-into-excel/4797514#4797514 — jtolle, Jun 09 '11 at 23:10

score 0 · Answer 1 · edited Dec 13 '12 at 17:51

A data validation doesn´t accept "union of ranges" (by example "=nameRange, name" or "=$I$4:$I$9;$A$21" would be a invalid input), but when you update the dialog box you can create a new range that include "Other "&Name and validate against this new range, or just append at the end of the range of validation the result of the dialog box.

Checking the web I device this quick hack (example below)

Formula1:=Range("I4").Value & "," & Range("I5").Value & ",a,b"

As you can see Formula1 accept string input in the format "option1,option2,option3", so this is easy: build a string with you validation range and append you your data from the dialog box.

Excel VBA: Named range + string value as validation list?

1 Answers1