Dependent Data Validation with VBA

  • Hi guys,
    I'm trying to set up two dependent data validation in a worksheet. Based on the selection in the first data validation cell, the second cell data validation list is adjusted.
    I've had a look at INDIRECT () function in other tutorials but my data is not set up to allow the use of that function so I'm turning to VBA as an alternative.

    In a simple Test sheet:

    First data validation cell has a dynamic named range in C3: =OFFSET(Data!$B$4,,,COUNTA(Data!$B$4:$B$30))
    Second data validation cell is created by this code in VBA in E3 (that I have found in another tutorial) that basically pulls the corresponding dynamic named range based on selection in C3.

    So far it works only in E3 depending on the choice in only C3......but if I have data validations going down to, say, can I populate the second data validation (by changing range in VBA??) to, for example, E10?
    Many thanks

  • Re: Dependent Data Validation with VBA

    Managed to extend first and second data validations by bloating up the WorksheetChange sub. Any way or ideas to shorten the code because I probably have >100 cells to validate?

    Many thanks.

  • Re: Dependent Data Validation with VBA

    Try this in your worksheet change code - don't see the need to add another procedure:

  • Re: Dependent Data Validation with VBA

    Thank you Stephen, that works.

    I've used the following because it helped bypass error in case Range("C3:C8") got emptied on the sheet by highlighting that range and selecting Clear Content.

    If RngX > 1 Then
    End If

    How can I adapt it or similar method to your code?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!