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: stackoverflow.com/questions/13860894/… — Byron Wall
– Byron Wall, Commented Jun 19, 2015 at 19:18

David G · Accepted Answer · 2015-06-19 19:17:05Z

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.

Collectives™ on Stack Overflow

VBA data validation to default value from named range

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related