VBA trigger for refreshing ODBC data in excel - Worksheet_Change vs workbook_open

Question

i have a connection to odbc that bring data to excel and the rest of the data i calculate manually in macro i created a macro but i need the macro to run whenever i click on the refresh button i put 1 macro in workbook_open() so that whenever the file opens for first time my macro get executed but when i click refresh button only the data from the odbc connection get updated i tried to invoke my macro by putting it inside worksheet_changed() event but it didn work here is what i tried to do in the worksheet_change event

   Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Target.Address = "$A$2" Or Target.Address = "$H$2" Then
   Call MyMacro
   End If
   End Sub

and here id MyMacro Sub MyMacro() Dim i As Integer Dim j As Integer j = 1 i = 2 Dim Counter As Integer Counter = 1

Do While Not IsEmpty(cells(i, 1).Value)
If cells(i, 5).Value >= 70 Then
 cells(i, 6).Value = Counter
 SLC = (Counter / 96) * 100
 cells(i, 7).Value = SLC
 Counter = Counter + 1
Else
 cells(i, 6).Value = 0
End If
i = i + 1
Loop
End Sub

MyMacro get called and reach the line where i update this cell

cells(i, 6).Value = Counter
but it calls the following event its like a cycle
Private Sub Worksheet_Change(ByVal Target As Range)

again no values gets updated

here is the an ex of how my excel sheet look like

TimeStamp               CA      CAAT       SL       Counter     SLC
11/24/2014 2:15         24      0         100.00    1           10.42
11/24/2014 2:30         27      0         100.00    2           11.46
11/24/2014 2:45         22      0         100.00    0           12.50
11/24/2014 3:00         21      0         100.00    3           13.54
11/24/2014 3:15         15      0         100.00    0           14.58
11/24/2014 3:30         18      1         94.44     4           15.63

the range from A:E get its values from my odbc connnection what am trying to do here fo SL counter whenever SL is greater or equal to 70 my counter get increased by 1 thank you in advance for your help

SierraOscar SierraOscar · Accepted Answer · 2014-11-25T11:20:51

Change your private sub to this

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Target.Address = "$A$2" Or Target.Address = "$H$2" Then
Application.EnableEvents = False
Call MyMacro
Application.EnableEvents = True
End If
End Sub

Your MyMacro sub is changing a cells value, and therefore triggering the Worksheet_Change event again. To overcome this, you need to switch events off before calling the macro and switch them on again afterwards.

If for whatever reason you do need to fire events while they are switched off, use the Application.DoEvents method.

VBA trigger for refreshing ODBC data in excel - Worksheet_Change vs workbook_open

1 Answers