Solved Special IF/THEN statement and Data Validation List in VBA

December 30, 2018 at 18:10:01
Specs: Windows 10
 The best way I can explain what I needs is this:if A1 = "" and A3 = "", then put a data validation list in cell A2.If A1 = "" and A3 <> "", A2 = A3. If A1 <> "" and A3 = "", then A2 = A1If A1 <> "" and A3 <> "", then A2 = A1I know I'm leaving out a lot of information, but if anyone could write this in VBA Code where it automatically updates when A1 or A3 are changes, I would appreciate it. I am working in Excel 2016, but I need this to work with Excel 2010 as well. I know, quite literally, nothing when it comes to coding.message edited by srcarter2012 See More: Special IF/THEN statement and Data Validation List in VBA

#1 December 30, 2018 at 19:34:59
 What do you mean by "put a data validation list in A2"?The list has to contain something. You need to tell us what that is. Is the list in a range of cells? Is it a list of hard-coded values?

#2
December 30, 2018 at 20:01:03
 If it were possible to write this in a formula in cell A2, it would go as follows:=if(and(A1="",A3=""),Named_Range,if(and(A1="",A3<>""),A3,if(and(A1<>"",A3=""),,A1,if(and(A1<>"",A3<>""),A1,""))))Where as "Named_Range" would make the cell a data validation list with the named range "Example 1". In every other instance, the data validation list would be deleted and would equal the respective cell. However, I know that this is not possible (I think) without using Worksheet Change in VBA Code. I am not familiar with writing VBA Code, but I do know you can write If/Then statements and Data Validation LIsts in VBA code. I just need to make that "Formula" in VBA Code in excel. December 31, 2018 at 07:41:57
 First, you can't have a space in a Range Name, so "Example 1" will not work. I used "Example_1" in the code below. I also left in all of the Validation options in case you want to set any of them.Try this code.```Private Sub Worksheet_Change(ByVal Target As Range) 'EnableEvents if Code Error occurs On Error GoTo CleanExit 'Determine which cell was changed If Target.Address = "\$A\$1" Or Target.Address = "\$A\$3" Then Application.EnableEvents = False 'If A1 <> "", A2 = A1 If Range("\$A\$1") <> "" Then With Range("A2") .Validation.Delete .Value = Range("\$A\$1") End With 'If A1="" and A3 <> "", A2 = A3 ElseIf Range("\$A\$1") = "" And Range("\$A\$3") <> "" Then With Range("A2") .Validation.Delete .Value = Range("\$A\$3") End With 'If A1 and A3 = "", Set A2 Validation List Else Range("\$A\$2") = "Please Select" With Range("A2").Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Example_1" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With End If End If 'EnableEvents before exiting CleanExit: Application.EnableEvents = True End Sub``` 