3 or More Dependent Data Validation, with VBA, easy to set up

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
5,271
Office Version
  1. 365
Platform
  1. Windows
I want to share a macro to set up multi dependent data validation with vba. Some versions of dependent data validation use formulas to set them up, usually with indirect functions. For 2 or 3 dependent data validation & small data set, it's pretty easy to set it up, but for more than 3, it might be difficult to maintain as it requires lots of tables & lots of helper columns. This version uses vba, you only need 1 table, 1 helper column & 1 named range. The code is a bit complicated but easy to set up and maintain.

This is an example of 3 dependent data validation, with VBA. You can easily set up more than 3 dependent data validations as needed.
Notes:
1. You only need 1 table as data validation source, 1 helper column, 1 named range & 1 simple formula in data validation (ie =xName).
2. The columns where data validation reside may or may not be contiguous.
3. The list in the table may have duplicate, empty & unsorted, but the code will make the list in data validation unique, sorted & non-empty. The list is also dynamic, you can add more data as needed.
4. In the range with data validation, changing or deleting cell contents will delete cells in the next column with data validation.
5. But one caveat of using macro is when macro changes/writes something on sheet it will delete Undo Stack, so at that time you can't use UNDO. In this case it happens every time you put the cursor in a cell with data validation.

The File:

dhee - multiple data validation non adjacent column 2.jpg


The code:
VBA Code:
Option Explicit
'=================================================================================================
'=============== ADJUST THE CODE IN THIS PART: ===================================

'sheet's name where the list for data validation is located. [in the example: sheet "sheet2"]
Private Const sList As String = "sheet2"

'table's name where the list for data validation is located. [in the example: "Table1"]
Private Const sTable As String = "Table1"

'sDT & sDV must be in correct order (in this example 'STATE > CITY > REP).
'You can add number of columns as needed.
'Column number on the table "Table1": 'STATE > CITY > REP
Private Const sDT As String = "1,2,4"

'Column where data validation is located 'STATE > CITY > REP
Private Const sDV As String = "B:B,D:D,G:G"

'the helper column, first cell
Private Const xH As String = "H1"

'the name range as the source of data validation
Private Const xN As String = "xName"
'==================================================================================================
'==================================================================================================
Private xOld As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Cells.CountLarge > 1 Then Exit Sub
    
If Not Intersect(Target, Range(sDV)) Is Nothing Then
    If isValid(Target) Then     'if activecell has data validation type 3
       If Target.Validation.Formula1 = "=" & xN Then 'its formula is "=xName"
       
       Dim d As Object, va, flag As Boolean, z, q, vb, x
       Dim i As Long, y As Long, w As Long
       
       Application.CutCopyMode = False 'prevent paste to the range with the DV
       xOld = Target.Value
       Set d = CreateObject("scripting.dictionary"):  d.CompareMode = vbTextCompare
       'columns with data validation:  sDV = "B:B,D:D,G:G"
       z = Application.Transpose(Application.Transpose(Split(sDV, ","))) ''create 1D array, variant/string type, Lbound = 1
       
       For i = 1 To UBound(z)
           If Target.Column = Range(z(i)).Column Then w = i: Exit For
       Next
  
       'reset xName to blank
'       ThisWorkbook.Names(xN).RefersTo = Sheets(sList).Range(xH)
       Sheets(sList).Range(xH).Name = xN  'blank cell

       If w > 1 Then 'if previous col with DV is empty then exit sub (with xName is blank)
           If ActiveSheet.Cells(Target.Row, z(w - 1)) = "" Then Exit Sub
       End If

          
           'Column number on the source table: sDT = "1,2,4"
            q = Evaluate("{" & sDT & "}") 'create 1D array, variant/double type, Lbound = 1
      
        'populate data from Table1,
        '"Application.Max(q)" is to limit the column range as needed for populating the list.
        va = Sheets(sList).ListObjects(sTable).DataBodyRange.Resize(, Application.Max(q)).Value
   
       For i = 1 To UBound(va, 1)
           flag = True
           
           If w = 1 Then 'if target is in first data validation column
               d(va(i, q(w))) = Empty
           Else
               'apply criteria from all previous column
               For y = 1 To w - 1
                   If UCase(va(i, q(y))) <> UCase(ActiveSheet.Cells(Target.Row, z(y))) Then flag = False: Exit For
               Next
               'if all criteria are met
               If flag = True Then d(va(i, q(w))) = Empty
           End If
       Next

            If d.Exists("") Then d.Remove ""
            If d.Count > 0 Then
                Dim c As Range
                Application.EnableEvents = False
                Application.ScreenUpdating = False
                    'populate d.keys to helper column & sort it
                    With Sheets(sList)
                        .Columns(.Range(xH).Column).ClearContents
                        Set c = .Range(xH).Resize(d.Count, 1).Offset(1)
                        c = Application.Transpose(Array(d.Keys))
                        c.Sort Key1:=c.Cells(1), Order1:=xlAscending, Header:=xlNo
                    End With
                   'populating range to xName
                    c.Name = xN
        
                Application.ScreenUpdating = True
                Application.EnableEvents = True
            End If
        End If
    End If
End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.CountLarge > 1 Then Exit Sub
    
If Not Intersect(Target, Range(sDV)) Is Nothing Then
    
    If isValid(Target) Then     'if activecell has data validation type 3
       If Target.Validation.Formula1 = "=" & xN Then 'if its formula is "=xName"
            If xOld <> Target.Value Then
            
                Dim i As Long, w As Long, z
            
                Application.EnableEvents = False
                'columns with data validation:  "B:B,D:D,G:G"
                z = Application.Transpose(Application.Transpose(Split(sDV, ",")))
                
                For i = 1 To UBound(z)
                    If Target.Column = Range(z(i)).Column Then w = i: Exit For
                Next
                     
                    'if it's not the last column with DV then clear all next column with DV
                    If w < UBound(z) Then
                        For i = w + 1 To UBound(z)
                            ActiveSheet.Cells(Target.Row, Range(z(i)).Column) = ""
                        Next
                    End If
                Application.EnableEvents = True
            End If
        End If
    End If
End If
    
End Sub

Sub toEnableEvent()
Application.EnableEvents = True
End Sub

Function isValid(f As Range) As Boolean
    Dim v
    On Error Resume Next
        v = f.Validation.Type
    On Error GoTo 0
    isValid = v = 3
End Function
 
You're welcome, glad to help & thanks for the feedback.:)
 
Upvote 0

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
5. But one caveat of using macro is when macro changes/writes something on sheet it will delete Undo Stack, so at that time you can't use UNDO. In this case it happens every time you put the cursor in a cell with data validation.
As a I said before, the macro to create dependent data-validation in post #1 has a caveat, which is it will delete the Undo Stack. So I decided to write another macro to deal with that issue.
Basically, the macro will create a helper table which will be used as the source of data-validation.

This is an example of 3 level dependent data validation, with a macro to create a helper table.
  • You can easily set up more than 3 level dependent data validation as needed.
  • You only need 1 main table as the source, 1 helper table (created by macro), 1 named range & 1 UDF.
  • The code will generate a non-empty, unique & sorted data validation list. The list is also dynamic, you can add data on the list without having to change the data validation formula.
  • Using the data-validation will not clear the UNDO history, except on the part with automatic deleting to preserve data consistency & when you create/recreate the helper table.
  • The columns may or may not be contiguous.
It will create a helper table:
table1 + helper table 1.jpg


Dependent data validation:

Data valid.jpg



The file: it contains the sample workbook + a manual (docx file)
 
Upvote 0
As a I said before, the macro to create dependent data-validation in post #1 has a caveat, which is it will delete the Undo Stack. So I decided to write another macro to deal with that issue.
Basically, the macro will create a helper table which will be used as the source of data-validation.

This is an example of 3 level dependent data validation, with a macro to create a helper table.
  • You can easily set up more than 3 level dependent data validation as needed.
  • You only need 1 main table as the source, 1 helper table (created by macro), 1 named range & 1 UDF.
  • The code will generate a non-empty, unique & sorted data validation list. The list is also dynamic, you can add data on the list without having to change the data validation formula.
  • Using the data-validation will not clear the UNDO history, except on the part with automatic deleting to preserve data consistency & when you create/recreate the helper table.
  • The columns may or may not be contiguous.
It will create a helper table:
View attachment 66531

Dependent data validation:

View attachment 66530


The file: it contains the sample workbook + a manual (docx file)
many thanks for providing all great contents
 
Upvote 0
Thanks for this! One question though, What is the purpose of the "manager" column, if it's not available for data validation?
 
Upvote 0
I tried adapting the code to work with my data, but even though the categories seem to work, I sometimes get a triangle error displaying the message: "Data validation error". It seems to be happening when I create a new row in the data validation table on the "Categories" sheet. Any ideas on what might be causing this and how to fix it?

Thanks.

 
Last edited:
Upvote 0
What is the purpose of the "manager" column, if it's not available for data validation?
It's just to show that the data validation list could come from non-consecutive columns.
I tried adapting the code to work with my data, but even though the categories seem to work, I sometimes get a triangle error displaying the message: "Data validation error". It seems to be happening when I create a new row in the data validation table on the "Categories" sheet.
I downloaded your file, added some new rows & data to the table then recreated the helper table. I couldn't reproduce the error.
I did it again few times and still no error.
Could you upload a sample file that raise the error as you described?
 
Upvote 0
Hi!
Great piece of code, helped me a lot!
I'm having trouble adapting the code for a different purpose. I want to combine the dependent data validation list (as of now 3 levels) with another dependent data validation list with only one "critera" column, that dictates data validation list on 4 other columns. So critera in Column A dictates the data validation list in B,C, D and E.
I tried multiplying the code so one sub for A > B, one for A > C, etc. But ran in to trouble. I should also say I'm just starting to dive in to the great world of VBA programming in excel

Any ideas on how to set up something like that?

Thanks
 
Upvote 0
@Stenis, welcome to the Forum.
I don't quite understand your description, but my understanding is like this:
1. There are 2 columns of data as the source of data-validation, say Country & City.
2. There are 5 columns to input data by data-validation, i.e col A:E. In col A you need the country list, in col B:E you need the city list base on selected country in col A.

Is that correct?
 
Upvote 0
Thank you!
Yes, kind of like that, but I have 5 columns of source data in my data validation source table, in my example: "Type of deviation", "Deviation rating", "Component criticality", "Recurrence frequency" and "Maintenance ability"

And 5 columns to input data by data-validation, A:E. Where the first column A dictates what values can be choosen in column B:E.

Previously I've used an IF statement in combination with a LOOKUP statement, but I see that it would be more flexible with VBA.

Please see attached image if that makes it clearer.

1670407023096.png
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top