Redirect Excel to a Macro when the button 'Refresh All' is pressed

[Pommy]

Hello
I was hoping to find a solution to my issue. I need to redirect the 'Refresh All' command in Excel so that it calls my Macro instead.
Preferabley something towards the idea of recognising a ‘Refresh All’ press & using some OnEvent code to redirect Excel to a specific macro. I haven’t found anything so clean as that yet, possibly not possible, but hoping some guru can work it out.
Failing that just disable the 'Refresh All' button completely.
Thank you.

[fsalina]

Why don't you place "ThisWorkbook.RefreshAll" before the procedures of your macro?

I'm sure it will do the same effect.

[Pommy]

I can't do what you suggest as i'm not refreshing the tables the way Excel would refresh them. I am sending selected dates to a SQL database and returning only the selected data. The database is quite literally enormous and unworkable to have all data come into Excel, so by the use selecting the date ranges they want it returns only a few months data rather than years of data. If the user has the ability to refresh all then the user is going to think they have refreshed all data, when in fact they haven't, they need to run my macro. I hope that makes sense. Her is the code i have which works well. When 'Refresh All' is selected i want Excel to run my macro instead (below)
Sub GetConnection()

Dim conn_old, where_clause, conn_new
Dim FY, FM, TY, TM
Dim parameter_sheet
Dim where_and
Dim conn_to_change
Dim start_conn, end_conn

conn_to_change = "N21 Summary File Import"
parameter_sheet = "I & E"

FY = ThisWorkbook.Sheets(parameter_sheet).Range("T5").Value
FM = Round((ThisWorkbook.Sheets(parameter_sheet).Range("U5").Value / 12), 4)
TY = ThisWorkbook.Sheets(parameter_sheet).Range("T6").Value
TM = Round((ThisWorkbook.Sheets(parameter_sheet).Range("U6").Value / 12), 4)

With ThisWorkbook.Connections(conn_to_change).ODBCConnection
conn_old = .CommandText
End With

If InStr(conn_old, "/* C1 */WHERE ") Then
where_and = "WHERE"
Else:
If InStr(conn_old, " WHERE ") > 0 Then
where_and = "AND"
Else: where_and = "WHERE"
End If
End If

where_clause = "/* C1 */" & _
where_and & " round(N21.YEAR + (convert(decimal(10,4), N21.MONTH) / 12),4) >= " & Round((FY + FM), 4) & _
" and round(N21.YEAR + (convert(decimal(10,4), N21.MONTH) / 12),4) <= " & Round((TY + TM), 4) & "/* C2 */"

If InStr(conn_old, "/* C1 */") > 0 Then
start_conn = Left(conn_old, (InStr(conn_old, "/* C1 */")) - 2)
Else: start_conn = Left(conn_old, (InStr(conn_old, "ORDER BY") - 2))
End If

end_conn = Right(conn_old, (Len(conn_old) - (InStr(conn_old, "ORDER BY") - 1)))

conn_new = start_conn & " " & where_clause & " " & end_conn

With ThisWorkbook.Connections(conn_to_change).ODBCConnection
.CommandText = conn_new
End With

ThisWorkbook.RefreshAll

End Sub