Adding Data Validation to ANY Cell Using an Excel User-Defined Function

 

In previous articles I have written about the connection between Excel UDFs and objects, I have noted that almost any object can be invoked through its use. So, it was with interest that I read a recent article on Debra Dalgleish’s website on the accidental deletion of dropdown arrows in cells with data validation lists when running macros.

https://contexturesblog.com/archives/2018/10/11/missing-data-validation-arrows/?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+contextures%2FdCfy+%28Contextures+Blog%29

In the article, she stated

“If you run a macro that deletes shapes on a worksheet, it might also delete the drop down arrow. Excel sees that arrow as a worksheet shape.”

Well, I just wrote this article where a UDF invoked filter arrows to emulate the properties of Excel new FILTER function.

https://dhexcel1.wordpress.com/2018/09/30/the-new-microsoft-filter-and-sort-functions-are-great-but-what-about-the-old-ones-no-one-know-existed/

So, I figured that, just maybe, a UDF could do the same thing with data validation. It CAN! And, it can add the data validation to any desired worksheet cell.

The following code is for the AddValidation VBA UDF:

Function AddValidation(vRange As Range, vList As Range)

On Error Resume Next

Range(vRange.Address).Validation.Add Type:=xlValidateList, _

Formula1:=”=” & vList.Address

End Function

Its use can be seen in the following figures. In the first, the formula

=AddValidation(A3,letters) is in cell F5. Actually, in the figure the 2nd argument in the UDF is J1:J3, but that is equivalent to the defined name range called letters.

AddVal1

The first argument points to cell A3 as the target for adding the data validation list. In the 2nd figure, you can see that the data validation drowdown arrow appears in A3. Pretty amazing!

AddVal2

I think you can see the myriad of possibilties that are possible by using this technique.

Free free to download the sample workbook at the link shown below. HTH!

ValidationUDF

Advertisements

1 thought on “Adding Data Validation to ANY Cell Using an Excel User-Defined Function

  1. XLarium

    Interesting. Some suggestions:
    (1) If I have =AddValidation(A3,J1:J3) in F3 and in F4 =AddValidation(A3,J7:J9) then the used DV in A3 is always the first I invoked. Either allow the DV to be overwritten or forbid AddValidation() to be used a second time on the same cell.
    (2) Add a UDF DeleteValidation(). Use it to switch DV on and off:
    =CHOOSE(E4,AddValidation(A4,J7:J9),DeleteValidation(A4))

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s