VBA data validation to default value from named range

Question

So I have a data validation in once cell that changes to one of three different named ranges based on a user selection in another cell. What I need is for when the user selects a certain value, i.e. "Selection A", the data validation will not only change to that corresponding named range, but will also display the first value within that range.

Currently I can manipulate the code to get the default value, however it keeps changing back to the default value every time I try and make a selection.Is this even possible? Below is my code that I'm currently running for once specific named range using a Worksheet_Change event

In this example I've used the named range selection_a

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("$E$3").Value = "Selection A" Then
    With Range("L3:R4").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=selection_a"
        .IgnoreBlank = False
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    End If

With the default value showing and changing back constantly:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("$E$3").Value = "Selection A" Then
    Range("$L$3").Value = Sheets("sheet2").Range("$M$4").Value    
    With Range("L3:R4").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=selection_a"
        .IgnoreBlank = False
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    End If

You should probably disable events inside your event handling code since you are changing cells. Related: http://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs/13861640#13861640 — Byron Wall, Jun 19 '15 at 19:18

score 1 · Accepted Answer · answered Jun 19 '15 at 19:17

In the example below I create a validation list in the cell A1 of Sheet1. The entries for the list are listed in K7 to K9.

With Sheet1("A1").Validation
    .delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=Sheet1!$K$7:$K$9"
End With

I'll use this format because I like it better. Now for your question:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Sheet1.Range("E3")) Then 'checks that E3 is the one that changed
        Application.EnableEvents = false
        If Range("$E$3").Value = "Selection A" Then 'list A
             With Sheet1("A1").Validation
               .delete
               .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                  xlBetween, Formula1:="=Sheet1!$K$7:$K$9" 'cells for list A
             End With
        ElseIf Range("$E$3").Value = "Selection B" Then' list B                 
             With Sheet1("A1").Validation
               .delete
               .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                  xlBetween, Formula1:="=Sheet1!$K$7:$K$9" 'cells for list B
             End With

        End if
    End if
    Application.EnableEvents = true

End sub

I'm pretty sure I got your cells wrong, but put the right ones as per the example I gave up top! Right now when E3 changed, it will create a new list depending on what was in E3. E3 itself should be a validation list in my opinion, and all possibilities should be represented in the worksheet change event.

Edit: I added the EnableEvent = False after seeing Byron's comment, without it would make itself fire in the execution.

Works like a charm! Thanks so much for the assistance! – tbg0001 Jun 19 '15 at 19:53 — tbg0001, Jun 19 '15 at 19:53

VBA data validation to default value from named range

1 Answers1