CODEWITHSUNDEEP
vbaexcel
JGR
JGR

Reputation: 191

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

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

Upvotes: 1

Views: 5283

Answers (1)

Comintern
Comintern

Reputation: 22185

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

Upvotes: 2

Related Questions