Excel Event after fetching external data

[ulfgod]

Hello,

I am using Excel 2010 and this is what I want to do in VBA:

1. Show a User Form.
2. Retreive data from Sql Server based on parameters from the Form.
3. Populate a WorkSheet with the selected data.
4. Hide the User Form
5. When the user Clicks The Refresh Button in the Data tab I would like to show the the User Form Again.

1-4 is no problem. I don't know how to solve number 5. I have been looking for an event to catch but haven't succeded . Anyone the that have done this ?

Thanks in advance.

 

[John_w]

See http://support.microsoft.com/kb/213187, although the code may be slightly different for Excel 2010 as the ListObjects collection (Worksheet.ListObjects.QueryTable) was added between a worksheet and its query tables in Excel 2007.

You could also try catching the refresh in the Worksheet_Change event.

 

[Gary McMaster]

If the event code John suggested doesn't work maybe you could cheat and change the button function.

I don't have 2010 but in earlier versions you can intercept a button click by changing it's "OnAction" property. Maybe that will do?

Gary

Public Sub Test()

Dim oControl As CommandBarControl

'Redirect "Refresh" button click to a custom procedure
For Each oControl In Application.CommandBars.FindControls(ID:=459)
   oControl.OnAction = "MyRefresh" ' Procedure "MyRefresh" must exist
Next oControl

'Uncomment to restore original functionality
'For Each oControl In Application.CommandBars.FindControls(ID:=459)
'   oControl.Reset
'Next oControl

'Also to intercept keyboard shortcuts see "Application.OnKey" in VBA help.

End Sub

Public Sub MyRefresh()

MsgBox "Refresh button clicked"
'Refresh query here

End Sub