Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Need to run a VBA Macro on data refresh in excel

Tags:

excel

vba

I am attempting to prompt a macro to run on a data refresh. I have the macro that needs to be run build, but I am having an issue with the new values not being used since the macros embedded in the sheet are called using ActiveX ComboBoxs.

I am finding several instances where people refer to AfterRefresh and BeforeRefresh, but I think I am misunderstanding how this would take effect and call a macro.

I currently am running ComboBoxs so I have multiple instances of

Private Sub ComboBox22_Change()
'do stuff
End Sub.

but I need the 'do stuff' to occur upon a data refresh, including refreshes that happen automatically and upon sheet open.

I don't want to tie the refresh to a specific box because the items that are refreshed are not dependent on any one instance of data change.

Any help is greatly appreciated.

Thank you.

like image 316
Rj Joplin Avatar asked Mar 14 '15 03:03

Rj Joplin


2 Answers

Maybe a worksheet change event would help in this situation.

Right Click the sheet tab, select "View Code", Select "Worksheet" then "Change."

Code will automatically kick in when a specific range of cells has been changed.

Right Click Sheet Tabenter image description here

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub    ' this stops code error if more than one cell is changed at once

    If Not Application.Intersect(Target, Me.Range("A1:C10")) Is Nothing Then    ' indicates the Target range
        MsgBox "You have changed " & Target.Address & " to " & Target
    End If

End Sub
like image 86
Davesexcel Avatar answered Nov 15 '22 07:11

Davesexcel


You could also use Worksheet_pivottableupdate event to run the macro. You set it up in a similar way to davesexcel answer above.

The connection in question may not be a pivot table but you can use a small and fast pivot table as a trigger.

Set the pivot table to update at the same time as your connection (e.g. set to self refresh every 5 minutes or on workbook open).

like image 36
Lyndon Penson Avatar answered Nov 15 '22 06:11

Lyndon Penson