Keep Page Visible In Workbook While Macros Run On Other Pages

[Buns1976]

Hi Everyone,
I have a workbook the contains 4 sheets. 3 of them are hidden. I have a macro that unhides those 3 sheets, does some calculations and then hides them.
You can see all of that as it takes place. Is there a way to make that not visible and keep the the 4th page visible as the macros run? I know I can
"Application.WindowState = xlMinimized" the workbook but I need it to be visible until it closes.

Thanks,
B

 

[breynolds0431]

Hi. Try Application.ScreenUpdating = False at the beginning of the code and turning it back on with Application.ScreenUpdating = True before the End Sub.

If that works for you, you should also add some precautionary error handling to turn screen updating back on in the event of an error occurring within your code. See below for an example of how that would work.

Sub dothings()

'Error handler to turn screen updating back on
On Error GoTo rThings

'Turns screen updating off
Application.ScreenUpdating = False

'here is where your code will go


'Turn screen updating back on after your code runs
Application.ScreenUpdating = True

Exit Sub

'Error Handling
ResetSettings:
MsgBox "The below error has occurred: " & vbCrLf & vbCrLf & "Error Number:" & Err.Number & vbCrLf & _
    "Error Description: " & Err.Description
Application.ScreenUpdating = True

End Sub

 

[Buns1976]

Hi. Try Application.ScreenUpdating = False at the beginning of the code and turning it back on with Application.ScreenUpdating = True before the End Sub.

If that works for you, you should also add some precautionary error handling to turn screen updating back on in the event of an error occurring within your code. See below for an example of how that would work.

Sub dothings()

'Error handler to turn screen updating back on
On Error GoTo rThings

'Turns screen updating off
Application.ScreenUpdating = False

'here is where your code will go


'Turn screen updating back on after your code runs
Application.ScreenUpdating = True

Exit Sub

'Error Handling
ResetSettings:
MsgBox "The below error has occurred: " & vbCrLf & vbCrLf & "Error Number:" & Err.Number & vbCrLf & _
    "Error Description: " & Err.Description
Application.ScreenUpdating = True

End Sub

Thanks. I should have mentioned that I DO have screen updating in the UPDATE macro. The UPDATE macro is calling other macros and I didn't have screen updating in those. I have since added it to those and it solved my problem. I assumed incorrectly that having screen updating in the UPDATE macro would apply to the other macros it runs but......

Thanks again!!
D

 

[Domenic]

I assumed incorrectly that having screen updating in the UPDATE macro would apply to the other macros it runs but......

Actually, that should be the case. You should only need to set ScreenUpdating to False in your main macro, the one calling your other macros. Then, as @breynolds0431 has already mentioned, you set it back to True at the end of your main macro.

 

[Buns1976]

Actually, that should be the case. You should only need to set ScreenUpdating to False in your main macro, the one calling your other macros. Then, as @breynolds0431 has already mentioned, you set it back to True at the end of your main macro.

That's what I was thinking as well but wasn't working that way?

 

[Domenic]

Maybe whatever it is that your code executes happens so quickly that it simply doesn't make a difference. As a test, try running the following code, where one procedure calls another, and the called procedure simply writes the value "xxx" to the first 500,000 cells in Columns A. You'll see that the code runs, and only when it finishes and returns to the calling procedure does the screen update.

Sub test()

    Application.ScreenUpdating = False
    
    doSomething
    
    Application.ScreenUpdating = True
    
End Sub

Sub doSomething()
    
    Dim i As Long
    For i = 1 To 500000
        Cells(i, 1).Value = "xxx"
    Next i
    
End Sub

 

[Buns1976]

Maybe whatever it is that your code executes happens so quickly that it simply doesn't make a difference. As a test, try running the following code, where one procedure calls another, and the called procedure simply writes the value "xxx" to the first 500,000 cells in Columns A. You'll see that the code runs, and only when it finishes and returns to the calling procedure does the screen update.

Sub test()



    Application.ScreenUpdating = False

   

    doSomething

   

    Application.ScreenUpdating = True

   

End Sub



Sub doSomething()

   

    Dim i As Long

    For i = 1 To 500000

        Cells(i, 1).Value = "xxx"

    Next i

   

End Sub

 

[Buns1976]

You may be onto something. I ran the code you supplied and its 35 seconds and the screen updating works . My sequence runs in 5 seconds. All but the Call IMPORT_HTML are simple procedures copy and paste or relocating a couple of things. Call IMPORT_HTML involves opening an HTML file in a local directory so just a touch longer.

Where I'm running into more of an issue is this workbook has a simple 4 cell query to the next workbook we use that runs a macro on workbook_open. That macro is basically the same as
the IMPORT_HTML above except it calls a different HTML file in the same local directory. The 4 cell query is set up to refresh on workbook open but it's hit and miss. I think again it may be a speed
issue and I noticed that if I do a manual refresh, it only takes a split second. I've tried the code below to slow it down by 10 seconds but doesn't seem to make much if any difference?

Sub Wait()
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 5
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

End Sub

 

[Domenic]

Application.Wait suspends all Excel activity, and could prevent other operations from being perform. So it might not be the right way to go. Instead, try using the following macro to pause your macro...

Sub PauseMacro(ByVal secs As Long)

    Dim endTime As Single
    endTime = Timer + secs
    
    Do
        DoEvents
    Loop Until Timer > endTime
    
End Sub

In your code, you would pause your macro by calling PauseMacro as follows...

Sub test()

    'your code
    '
    '
    
    PauseMacro 5 'seconds
    
    '
    '
    '
    
End Sub

Hope this helps!

 

[Buns1976]

Application.Wait suspends all Excel activity, and could prevent other operations from being perform. So it might not be the right way to go. Instead, try using the following macro to pause your macro...

Sub PauseMacro(ByVal secs As Long)

    Dim endTime As Single
    endTime = Timer + secs
   
    Do
        DoEvents
    Loop Until Timer > endTime
   
End Sub

In your code, you would pause your macro by calling PauseMacro as follows...

Sub test()

    'your code
    '
    '
   
    PauseMacro 5 'seconds
   
    '
    '
    '
   
End Sub

Hope this helps!

Thank You!! I'm assuming I can run the pause in the first line on workbook_open correct? That should give the workbook enough time to refresh the query I would think.