14

I'm working on a VBA project based in a workbook. The code opens a new workbook and calls an external API which downloads and inserts a bunch of data in multiple worksheets of this new workbook. I deactivated Screen Updating (Application.Screenupdating = False) so initially the screen stays focused on the original workbook while the API downloads data on the other workbook in the background. However, the screen switches to the new workbook once the API inserts data. How can I prevent this from happening? Thanks!

Gaffi
  • 4,307
  • 8
  • 43
  • 73
lodhb
  • 929
  • 2
  • 12
  • 29

4 Answers4

29

Hiding the active workbook is possible with

ActiveWorkbook.Windows(1).Visible = False

You may need to replace ActiveWorkbook with an appropriate reference if the workbook in question is not the active one and/or add a loop like For i = 1 To ActiveWorkbook.Windows.Count if the workbook has multiple windows.

Matt Donnan
  • 4,933
  • 3
  • 20
  • 32
Paul B.
  • 2,394
  • 27
  • 47
  • +1 This method is simple to use and you can simply run it again with "True" once the code has finished executing – Matt Donnan Jul 06 '12 at 09:07
5

You could try using the ShowWindow API function:

Public Declare Function ShowWindow Lib "user32.dll" _
    (ByVal HWND As Long, ByVal nCmdShow As Long) As Long

Const SW_HIDE as Long = 0
Const SW_SHOW as Long = 5

ShowWindow otherWorkbookApplication.Hwnd, SW_HIDE

'Your code here

ShowWindow otherWorkbookApplication.Hwnd, SW_SHOW

Or alternatively, the LockWindowUpdate API function (thanks to Chip Pearson, http://www.cpearson.com/excel/vbe.aspx):

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
    (ByVal ClassName As String, ByVal WindowName As String) As Long

Private Declare Function LockWindowUpdate Lib "user32" _
    (ByVal hWndLock As Long) As Long


Sub EliminateScreenFlicker()
    Dim VBEHwnd As Long

    On Error GoTo ErrH:

    Application.VBE.MainWindow.Visible = False

    VBEHwnd = FindWindow("wndclass_desked_gsk", _
        Application.VBE.MainWindow.Caption)

    If VBEHwnd Then
        LockWindowUpdate VBEHwnd
    End If

    '''''''''''''''''''''''''
    ' your code here
    '''''''''''''''''''''''''

    Application.VBE.MainWindow.Visible = False
ErrH:
    LockWindowUpdate 0&
End Sub
Community
  • 1
  • 1
mkingston
  • 2,678
  • 16
  • 26
  • Note that Chip Pearson's code uses FindWindow to get the handle of the VB Editor. You could ignore the FindWindow part and just use the Hwnd property of the application object of your target workbook as the argument to LockWindowUpdate. I've left it there so you have both alternatives. – mkingston Jul 06 '12 at 01:57
  • 1
    "ShowWindow " API is better, because it will not have any side effect, compare to "ActiveWorkbook.Windows(1).Visible = False". In my test, once a Workbook is saved with ".Windows(1).Visible = False", it need to set Visible back to True before you can view the data. – bobyuan Jan 18 '15 at 12:57
3

You either need to save and close the target workbook, or select the original workbook before screenupdating is turned back on.

Jon Crowell
  • 21,695
  • 14
  • 89
  • 110
0

Sub HidePERSONAL()
Dim i
On Error Resume Next 'in case something crazy happens
For i = 1 To Windows.Count
'Debug.Print Windows(i).Visible
'Debug.Print Windows(i).Caption
If Windows(i).Caption = "PERSONAL.XLSB" Then
Windows(i).Visible = False
On Error GoTo 0
Exit For
End If
Next i
End Sub

RayInAZ
  • 1
  • 1