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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

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,071
Messages
5,545,818
Members
410,707
Latest member
SanTrapGamer
Top