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.
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.
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
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).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With