Background Query Refresh in Hidden Tab

Topic

New Reply

I’ve created a spreadsheet in Excel 2016 that has an Excel Query to a SQL database configured on a hidden sheet. The query to set to refresh when the file is open. While the file is being opened, Excel “selects” the query result cells on the hidden sheet during the refresh. The sheet remains hidden (not visible and no tab at the bottom), but somehow has the focus. You can see this in the screenshoot when the shading (the selected cells) does not correspond with the cells on the visible sheet. While this supposedly “hidden” sheet has the focus it is possible to interact with it, including adding or deleting data (or the entire query!). Clicking on one of the tabs at the bottom of the screen returns the focus to that tab and the hidden sheets are no longer accessible.

46275-2016-12-19_17-24-44

I’ve tried protecting the hidden sheet, but that blocks the query from refreshing when the file is opened.

Any thoughts on how to properly protect my query from accidental deletion?

Reply | Quote

Replies

R.R.,

Not sure how you’re refreshing the query on open but if you are using an Auto_Open macro you can just finish it by selecting a visible sheet and cell to change the focus. If not you can create one and then change the query to not auto refresh and use a Refresh.All in your macro.

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

rrdavis,

You could program a form to open when the workbook opens with Modal set to True then close it when the query has completed. The user will not be able to interact with the sheets while the form is visible. The code removes the “X” from the form so the user cannot prematurely close it (found a ways back on the internet somewhere). After an adjustable number of seconds, the automatically closes releasing the focus to the sheet.

HTH,
Maud

46329-QUERY1

In the ThisWorkbook module or Auto_Open macro as RG suggests:

Code:

Private Sub Workbook_Open()
    UserForm1.Show
End Sub

In the userform’s module

Code:

[COLOR=”#008000″]’——————————-
‘FIND THE UERFORM’S WINDOW[/COLOR]
Private Declare Function FindWindow Lib “user32” _
        Alias “FindWindowA” ( _
        ByVal lpClassName As String, _
        ByVal lpWindowName As String) As Long
[COLOR=”#008000″]’——————————-
‘GET THE CURRENT WINDOW STYLE[/COLOR]
Private Declare Function GetWindowLong Lib “user32” _
        Alias “GetWindowLongA” ( _
        ByVal hWnd As Long, _
        ByVal nIndex As Long) As Long
[COLOR=”#008000″]’——————————-
‘SET THE NEW WINDOW STYLE[/COLOR]
Private Declare Function SetWindowLong Lib “user32” _
        Alias “SetWindowLongA” ( _
        ByVal hWnd As Long, _
        ByVal nIndex As Long, _
        ByVal dwNewLong As Long) As Long
[COLOR=”#008000″]’——————————-[/COLOR]
Const GWL_STYLE = -16
Const WS_SYSMENU = &H80000


Private Sub UserForm_Initialize()
   Dim hWnd As Long, lStyle As Long
[COLOR=”#008000″]’——————————-
‘WHICH TYPE OF USERFORM[/COLOR]
   If Val(Application.Version) >= 9 Then
      hWnd = FindWindow(“ThunderDFrame”, Me.Caption)
   Else
      hWnd = FindWindow(“ThunderXFrame”, Me.Caption)
   End If
[COLOR=”#008000″]’——————————-
‘GET THE CURRENT WINDOW STYLE AND TURN OFF THE CLOSE BUTTON[/COLOR]
   lStyle = GetWindowLong(hWnd, GWL_STYLE)
   SetWindowLong hWnd, GWL_STYLE, (lStyle And Not WS_SYSMENU)
End Sub


Private Sub UserForm_Activate()
[COLOR=”#008000″]’——————————-
‘WAIT X SECODS THEN CLOSE FORM[/COLOR]
    dWait = TimeValue(“0:00:10″) [COLOR=”#008000”]’10 SEC WAIT.  ADJUST TO QUERY REFRESH[/COLOR]
    Application.Wait (Now + dWait)
    UserForm1.Hide
End Sub

Reply | Quote

Maud

..a very nice refreshing solution!

Reply | Quote