CODEWITHSUNDEEP
excelonchangevba
Nameishi
Nameishi

Reputation: 305

VBA drop downlist onchange

Hey Id like to know if it is feasible : currently I have a drop down list on sheet1 that contains a list of names being pulled from sheet2 using data validation. The cell next to that drop down list is populated with a phone number using VLOOKUP from sheet2.
My question : Can I use VBA so that everytime there is a change in the drop down list the cell next to it will become populated from data in SHEET2? keep in mind the value that is selected is needed to pull the proper phone number .

Why am i asking if my file works? Because anyone can accidently delete the VLOOKUP formula and I can not Protect it because it is a shared document that employs several different macros.

Upvotes: 2

Views: 4641

Answers (1)

Andrew
Andrew

Reputation: 64

Something like this? (Source: https://support.microsoft.com/en-us/help/213612/how-to-run-a-macro-when-certain-cells-change-in-excel)

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("A1")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
       Is Nothing Then

    ' Display a message when one of the designated cells has been
    ' changed.
    ' Place your code here.
    Range("B1").Formula = "=VLookup(A1,LookupTable,2,FALSE)"

    End If
End Sub

Upvotes: 1

Related Questions