2

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?

Vaccano
  • 6,696

4 Answers4

3

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.

dkusleika
  • 1,836
  • Complex queries cannot use parameters in Excel. Just one of those things Microsoft did not think you would want to do. – Vaccano Oct 22 '10 at 16:16
1

Put:

application.volatile

in your code.

Sux2Lose
  • 3,327
0

For sake of clarity I am going to say that the parameter is in CELL A1 and the result of the VBA code is listed in CELL A2.

In VBA module

Function Test1(strText as string) as string
   Test1 = strText & "15"
End Function

In Cell A2 place the equation "=Test1(A1)
In Cell A1 place the parameter you want to change

I would verify it works and then place your other VBA code in to have the code run whenever A1 changes.

wbeard52
  • 3,447
0

dkusleika's answer looks best; things get a little trickier if you want to to do the same for a range. The following code will occur whenever a cell within the specified area is modified.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim woArea As Range, isect As Range
Set woArea = Sheet1.Range("A1:A500")
Set isect = Application.Intersect(Target, woArea)

If isect Is Nothing Then
    ' Nothing happens
Else
    ' Stuff you want to happen goes here
End If

End Sub
Michael
  • 1,173