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 = A1
If A1 <> "" and A3 <> "", then A2 = A1

I 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

Reply ↓  Report •

#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?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#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.


Reply ↓  Report •

#3
December 31, 2018 at 07:41:57
✔ Best Answer
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

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •
Related Solutions


Ask Question