Basics of how to make a VBA procedure run in excel when a Cell is changed

I am trying show some data from my SQL Server in excel. Because I have a query that hits 3 tables and uses a parameter, I am going to have to use VBA.

I am not sure how to get the VBA to "auto run". Doing a normal query in Excel gives you options like running when the sheet is open or auto refreshing. Since I am adding a parameter, I would like the my Sub to run when I change the value of the cell the parameter is in.

Any ideas?

Answer

Right click on the sheet tab and choose View Code. Put code like this in the sheet's module

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$A$1" Then 'only run if the correct cell is changed
        MySub 'this is the name of your vba procedure
    End If

End Sub

This sub will run whenever a cell on that sheet is changed. If the cell that's changed is A1 (change to suit your data), then a procedure in a standard module named MySub (change to suit) will be run.

I'm not sure why you have to use VBA though. It seems like you could write SQL to pull from as many tables as you want and include a parameter.