Forum Discussion

legendsaud's avatar
legendsaud
Copper Contributor
Dec 15, 2024
Solved

Can you Automatically Update Formulas and Data Validation Between 2 cells in Excel Using VBA

In Excel, I have the following formula in cell D14: =LET(   lookupValue, $D$13,   Name, VLOOKUP(lookupValue, Cust_Tbl, 2, 0),   IF(lookupValue<>"", IF(Name<>"", Name, ""), "") ) This formul...
Formulas and Functions
Macros and VBA
Office Scripts
  • legendsaud's avatar
    legendsaud
    Dec 31, 2024

    Thanks, and sorry for the late reply. I like your approach and it will probably work. However, following is the code that works and requires lesser steps:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    On Error GoTo ExitHandler

    If Not Intersect(Target, Range("D13")) Is Nothing Then
        ' Set formula in D14 when D13 changes
        Range("D14").Formula = "=LET(lookupValue,$D$13,Name,VLOOKUP(lookupValue,Cust_Tbl,2,0),IF(lookupValue<>"""",IF(Name<>"""",Name,""""),""""))"
    ElseIf Not Intersect(Target, Range("D14")) Is Nothing Then
        ' Set formula in D13 when D14 changes
        Range("D13").Formula = "=IF(D14<>"""",XLOOKUP(D14,Cust_Tbl[Customer Name],Cust_Tbl[Contact]),"""")"

    End If

ExitHandler:
    Application.EnableEvents = True
End Sub

    It turns out data validation didn't need to be explicitly declared everytime.

NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Dec 17, 2024

I haven't been able to test the code, but I think it could work.

The proof of the pudding is in the eating 🙂. 

Hope this helps, if not please just ignore it.

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Monitor changes in D13 and D14
    If Not Intersect(Target, Range("D13")) Is Nothing Then
        If Target.Value <> "" Then
            ' When D13 changes, update D14 with the name lookup formula
            Range("D14").Formula = "=LET(lookupValue, $D$13, Name, VLOOKUP(lookupValue, Cust_Tbl, 2, 0), IF(lookupValue<>"""", IF(Name<>"""", Name, ""),""))"
            
            ' Set data validation for D13
            With Range("D13").Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="=Cust_List"
                .IgnoreBlank = True
                .InCellDropdown = True
            End With
        End If
    ElseIf Not Intersect(Target, Range("D14")) Is Nothing Then
        If Target.Value <> "" Then
            ' When D14 changes, update D13 with the contact lookup formula
            Range("D13").Formula = "=LET(lookupValue, $D$14, Contact, VLOOKUP(lookupValue, Cust_Tbl, 1, 0), IF(lookupValue<>"""", IF(Contact<>"""", Contact, ""),""))"
            
            ' Set data validation for D14
            With Range("D14").Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="=Cust_List_Name"
                .IgnoreBlank = True
                .InCellDropdown = True
            End With
        End If
    End If
End Sub

 

legendsaud's avatar
legendsaud
Copper Contributor
Dec 31, 2024

Thanks, and sorry for the late reply. I like your approach and it will probably work. However, following is the code that works and requires lesser steps:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    On Error GoTo ExitHandler

    If Not Intersect(Target, Range("D13")) Is Nothing Then
        ' Set formula in D14 when D13 changes
        Range("D14").Formula = "=LET(lookupValue,$D$13,Name,VLOOKUP(lookupValue,Cust_Tbl,2,0),IF(lookupValue<>"""",IF(Name<>"""",Name,""""),""""))"
    ElseIf Not Intersect(Target, Range("D14")) Is Nothing Then
        ' Set formula in D13 when D14 changes
        Range("D13").Formula = "=IF(D14<>"""",XLOOKUP(D14,Cust_Tbl[Customer Name],Cust_Tbl[Contact]),"""")"

    End If

ExitHandler:
    Application.EnableEvents = True
End Sub

It turns out data validation didn't need to be explicitly declared everytime.

Resources