Excle VBA - "Screenupdating=True" malfunction: Screen doesn't refresh (e.g. to change worksheet view)

Question

I'm having issues with a workbook not changing view to a newly unhidden/activated worksheet in-between toggling screen updating from off to on and back off again - it's not actually refreshing the screen. (code below)

I have module that calls forms and subs from a main sub to carry out a number of tasks - within each of the subs (except the main) I turn off screen updating in the beginning and turn it on at the end (example below). The workbook has a number of worksheets (mostly hidden) that are used for processing or as the final visible view - when opened, only one sheet is visible (used to launch the main sub).

While running, one of the subs unhides and activates the final worksheet, then deletes the starting worksheet - and toggles screen updating back on ("True"), and exits back to the main sub (which calls another sub again).

But it doesn't change view, the view stays on the deleted sheet while the subs all finish. (I can "trick" it into changing view to the newly active worksheet by inserting a "MsgBox" - but don't want to do that.)

This has been a hard one for me to search out answers (because looking up "Screenupdating" and anything else brings up a myriad of answers regarding "how to stop the screen from updating").

Sub createADS()
Dim oneForm As Object
    Set MainWrkBk = ActiveWorkbook
    cancel = False 'initialise
Call ADSheaderFormShow
    Set MainWrkBk = ActiveWorkbook 're-Set MainWrkBk after doing "SaveAs" in previous form
Call ADSformGen
    MainWrkBk.Worksheets("ADSform").Activate 'Doesn't change view
'MsgBox "Enter antenna information from RFDS"
'^^^ Tricks it into refreshing worksheet when active
Call ADSinputFormShow
Call ADSsetAntennas
Call ADSpullData

GoTo ExitHandler

ExitHandler:
    For Each oneForm In UserForms
        Unload oneForm
        ThisWorkbook.Save
    Next oneForm
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True   
End Sub

Private Sub ADSformGen()
    Application.ScreenUpdating = False 'Returned to True after running sub
MainWrkBk.Worksheets("HidDbSh").Visible = True

MainWrkBk.Worksheets("HidDbSh").Cells(1, 1).Value = "Site Info"
MainWrkBk.Worksheets("HidSiteTemp").Range("a1").CurrentRegion.Copy _
    Destination:=MainWrkBk.Worksheets("HidDbSh").Cells(2, 1)
    Columns.AutoFit
    Application.Calculation = xlCalculationAutomatic 'to reset all formula calcs before deleting source
MainWrkBk.Worksheets("HidDbSh").Visible = False

Application.DisplayAlerts = False
MainWrkBk.Worksheets("HidSiteTemp").Delete
Application.DisplayAlerts = True

MainWrkBk.Worksheets("HidADSform").Visible = True
MainWrkBk.Worksheets("HidADSform").Name = "ADSform"
With MainWrkBk.Worksheets("ADSform").UsedRange
        .Copy
        .PasteSpecial Paste:=xlPasteValues, _
        Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
End With

Application.DisplayAlerts = False
MainWrkBk.Worksheets("BlankADSForm").Delete
Application.DisplayAlerts = True

MainWrkBk.Worksheets("ADSform").Activate
    MainWrkBk.Worksheets("ADSform").Range("B2").Select

Application.ScreenUpdating = True

End Sub

Immediately after the `Application.ScreenUpdating = True` that is intended to update the screen off the deleted worksheet, put a `DoEvents` command. The message pump may be so crammed with backlogged queued commands that your screen update is getting lost. Also consider some judiciously applied [With ... End With statement](https://msdn.microsoft.com/en-us/library/wc500chb.aspx) to reduce the repetitive parent workbook and worksheet references made in ADSformGen. — , Mar 23 '16 at 01:10
@Jeeped Thank you (+1) `DoEvents` is news to me, and is **awesome** - that works like a charm. I was looking it up some before I entered it, and someone mentions to take care using it (watch where you put it and what calls it?). I'm not sure what the caveats are with it, but I put it immediately before [the "End Sub" line in my example above] and it just flips over. TYVM. — JGR, Mar 23 '16 at 14:45
@Jeeped As far as "With ... End With" statements (and clean coding) goes: I'm still not savvy (_at all_) with VBA, and am struggling with stuff like that as well as _not_ activating sheets/workbooks (or cells/ranges) all the freaking time. I would **love** to learn better practices, and suggestions on resources that point me in the right direction (aside from Google searches) are certainly welcome and very appreciated. I am reading the link you posted and trying to wrap my head around that ATM, though - thank you again. — JGR, Mar 23 '16 at 14:50
See [How to avoid Select/Activate methods in Excel VBA macros](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — David Zemens, Mar 23 '16 at 19:11
This may or may not help for your specific situation, but there is a known issue with Excel 2016 screen refresh. The following link points to the KB update to fix the issue. It worked for me. https://support.microsoft.com/en-us/help/4011165/september-12-2017-update-for-excel-2016-kb4011165 — Mike S, Sep 22 '17 at 20:36
@JGR, as @Jeeped advised use `With...End With` to avoid using `.Select`, which is a no-no in VBA, except for debugging. — I. Я. Newb, Nov 16 '18 at 22:22

Comintern · Answer 1 · 2016-03-23T22:27:43.093

2

If you want to ensure that the screen updates when you active the sheet, turn screen updating on before you active it. Otherwise, you risk the redraw event that the Activate call will generate getting swallowed:

    '...
    Application.ScreenUpdating = True
    MainWrkBk.Worksheets("ADSform").Activate
    MainWrkBk.Worksheets("ADSform").Range("B2").Select    
End Sub

edited Mar 23 '16 at 22:27

answered Mar 23 '16 at 02:06

Comintern

21,855
5
33
80

In the Main sub, with no references to "Screenupdating" changes - where screen updating should be always on - I have these lines: `Call ADSformGen`; `MainWrkBk.Worksheets("ADSform").Activate`; `MainWrkBk.Worksheets("ADSform").Range("B2").Select`; `'MsgBox "Enter antenna information from RFDS"`; `Call ADSinputFormShow`. Plus, I've tried it immediately following a change to "True" within the lower subs w/o change. – JGR Mar 23 '16 at 14:29
I understand what you're saying, though - it's **supposed** to work that way (right??), it's just not flipping over. – JGR Mar 23 '16 at 14:46
2

@JGR - It *is* supposed to work that way. If the message handler is swamped, you could try explicitly calling for a `ScreenRefresh`. See the edit. – Comintern Mar 23 '16 at 17:23
I don't seem to have an `Application.ScreenRefresh`. – JGR Mar 23 '16 at 20:38
1

@JGR - Doh! Apparently that's Word only - rolled back. – Comintern Mar 23 '16 at 22:27

Excle VBA - "Screenupdating=True" malfunction: Screen doesn't refresh (e.g. to change worksheet view)

1 Answers1