0

I'm using the following code (reduced to relevant parts for simplicity):

Sub deleteCategory()
    Application.ScreenUpdating = False
        Sheets("Recurring Expenses").Activate
        'A bunch of stuff is checked and done on the now activated sheet
        Sheets("Input").Activate 'This is the sheet the sub is called from via a button
    Application.ScreenUpdating = True
End Sub

Although Application.ScreenUpdating is turned off, every time you click the button and the macro runs (otherwise bug-free) you can clearly see the Sheet "Recurring Expenses" briefly flash for a moment. Any idea what causes this or how it could be fixed?

riskypenguin
  • 2,139
  • 1
  • 10
  • 22
  • 1
    Do not use `.Activate` or `.Select` See here for more info on avoiding the use: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – Scott Craner Jan 25 '18 at 23:33
  • Declare your worksheets. `Dim ws As Worksheet _ Set ws = ThisWorkbook.Worksheets(Recurring Expenses")`. Or you can follow the link provided by @ScottCraner. – K.Dᴀᴠɪs Jan 25 '18 at 23:40
  • 1
    show the line that errors, I bet you did not fully qualify the range objects. – Scott Craner Jan 25 '18 at 23:41

1 Answers1

0

Try using With statement:

Sub deleteCategory()

With Excel.Application
.ScreenUpdating = False
.EnableEvents = False
End With

    With Workbooks("your_workbook_name").Sheets("Recurring Expenses")
    ' do stuff like
        ' .copy
        ' .cells.select
    End With

    'A bunch of stuff is checked and done on the now activated sheet

    With Workbooks("your_workbook_name").Sheets("Input") 'This is the sheet the sub is called from via a button
    ' Do stuff
    End With

With Excel.Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub

Hope this help you.

warner_sc
  • 848
  • 6
  • 13