Apply Data Validation with VBA

[Gliffix101]

Hey All,

Looking for some help here. I have two cells: C46 and E46. In Cell C46 I have a data validation based on named range "Headers". What I'm looking to do is that once a user selects an option in C46, the VBA applies a data validation in E46 with a previously defined named range. For example: If user selects drop down C46 = "Specialist", then E46 should apply data validation with the named range "LS". The macro will not define the named range as I have this defined within the Excel workbook. I've been working through the code from previous posts, but it's just not applying the validation. Any help would be appreciated:

Private Sub Worksheet_Change(ByVal Target As Range)
      
    If C46 = "Loan Specialist" Then
        With E46.Validation
            .DELETE
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=LS
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = False
        End With
    End If


End Sub

 

[mikerickson]

You can do this with Dependent Named ranges.

You already have the Named Range LS, so select E46 and define a new Name

Name:= Foo RefersTo = =IF($D46="Specialist", LS)

Then put List style Data Validation on E46 with the source =Foo

 

[Gliffix101]

So simple! I have 9 validation points, so I was trying to input the formula initially into the Data Validation formula. Never even thought to put the IF statement as part of the named range! Now I see why you're an MVP! Thanks Mike!!!