Data Validation - Worksheet change VBA

bravia

New Member
Joined
Apr 2, 2020
Messages
6
Office Version
  1. 365
Platform
  1. MacOS
Hi there,

I'm very new to the VBA, and have a reasonable Excel knowledge of formulas etc.

I have a sheet that I use an Array Formula to give me 4 options which is referenced in a range by a data validation drop down list (each row has it's own range & result.).

The Vlookup is based on the value in col D, (searches on sheet 'Fixture Library' and result is produced in cols N-Q, there will only be a maximum of 4 results. The data validation references the N-Q range for each row. This all works well.

The next step is what I'm struggling with - I have found a great VBA script online that on clicking on the data validation cell will refresh the result to the first. I would like instead of clicking on the cell with the validation that anytime there is a change in col D, it refreshes it's result in col F. Is this possible can anyone help with the script?

Example file is here: Dan Young sent you 1 item. - A number of cols are hidden/deleted to prevent confusion.

Script I am currently using:
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Updateby Extendoffice 20160725
    Dim xFormula As String
    On Error GoTo Out:
    xFormula = Target.Cells(1).Validation.Formula1
    If Left(xFormula, 1) = "=" Then
        Target.Cells(1) = Range(Mid(xFormula, 1)).Cells(1).Value
    End If
Out:
End Sub

••••ˇˇˇˇ
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

QuietRiot

Well-known Member
Joined
May 18, 2007
Messages
1,077
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xFormula As String
    
    ' if unit # changes
    If Target.Column = 1 Then
        xFormula = Range("F" & Target.Row).Validation.Formula1
        Range("F" & Target.Row).Value = Range(Mid(xFormula, 1)).Cells(1)
    End If
    
End Sub
 

bravia

New Member
Joined
Apr 2, 2020
Messages
6
Office Version
  1. 365
Platform
  1. MacOS
Thanks! That works perfectly! Since this is working it's brought up another little tweak I could do with some help with.... If a cell in col A is empty it doesn't update col F with an empty value, is it possible to include that in the script?
 

Watch MrExcel Video

Forum statistics

Threads
1,114,277
Messages
5,546,935
Members
410,764
Latest member
Dedeke
Top