Refresh without physically moving to the data tab? VBA code listed.

[zpsale01]

I have an activeX Combobox that when a change event occurs it refreshes a data tab titled Expenses.

this refresh uses the hypretrieve smartview VBA script.
this function currently works but when the refresh occurs the user is moved to the data sheet while it refreshes and then back to the report.

is it possible to make the refresh occur in the background?

also, if i wanted to hide the sheet, could i unhide refresh, then hide again, all while the user can only see the report screen?

VBA code below.

Actual Macro

Declare Function HypRetrieve Lib "HsAddin" (ByVal vtSheetName As Variant) As Long
Sub RetrieveData()
sts = HypRetrieve("Expenses")

End Sub

Combobox Change Event

Private Sub ComboBox1_Change()
Call RetrieveData
End Sub

Thanks for all your insight!!!

greatly appreciated.

 

[pbornemeier]

Try this:

Declare Function HypRetrieve Lib "HsAddin" (ByVal vtSheetName As Variant) As Long
'Actual Macro
Sub RetrieveData()
    Application.ScreenUpdating = False
    Worksheets("Expenses").Visible = True
    sts = HypRetrieve("Expenses")
    Worksheets("Expenses").Visible = False
    Application.ScreenUpdating = True
End Sub

'Combobox Change Event
Private Sub ComboBox1_Change()
    Call RetrieveData
End Sub

 

[ghrain22]

try turning off screenupdating:
Application.ScreenUpdating = False