Application.screenupdating set to false doensn't work

0
votes

So, I'm new to VBA and I have this code that adds a new row to two tables, one to a table on sheet3 which is visible and the other to a table on sheet5 which is hidden. My problem now is, with ScreenUpdating set to false at the beginning ad true at the end, when the code is run, the screen still flickers showing hidden sheets. How do I get this flickering to stop?

            Sub Add_Observation()
            '
            Application.ScreenUpdating = False
            Sheet3.Activate
            ActiveSheet.Unprotect
            Sheet5.Visible = True
                    Dim Ar As Integer, Br As Integer, A As Integer
                    '
                    Ar = Columns("B").Find(What:="S/N", After:=Range("B26")).Row 'Searching row of "S/N" header
                    Br = Range("B" & Ar).End(xlDown).Row 'Searching last row in EQUIPMENT table
                    A = (Br + 4)
                    B = (Br + 5)

                    Sheet5.Select
                    Dim Qr As Integer, Dr As Integer, D As Integer, D1 As Integer
                    '
                    Qr = Columns("B").Find(What:="S/N", After:=Range("B16")).Row
                    Dr = Range("B" & Qr).End(xlDown).Row
                    D = (Dr + 4)
                    D1 = (Dr + 2)


               Sheet3.Select
                Cells(Dr, "C").Select

            If Range(Cells(Ar, 1), Cells(A, 1)).EntireRow.Hidden = False Then

                                                                            Sheet3.Activate
                                                                                Rows(A).Insert Shift:=xlDown  'Inserting new row
                                                                                Cells(Br + 1, "B") = Cells(Br, "B") + 1 'Adding a sequential Number
                                                                                Rows(Br).Copy 'Copying format of last row
                                                                                Rows(Br + 1).PasteSpecial Paste:=xlPasteFormats 'Pasting format to new row
                                                                                Application.CutCopyMode = False 'Deactivating copy mode

            Sheet5.Select
            If Range(Cells(Qr, 1), Cells(D, 1)).EntireRow.Hidden = False Then
                                                                            Sheet5.Activate
                                                                                Selection.Rows(D).Insert Shift:=xlDown  'Inserting new row
                                                                                Rows(D1).Insert Shift:=xlDown
                                                                                Cells(Dr + 1, "B") = Cells(Dr, "B") + 1 'Adding a sequential Number
                                                                                Rows(Dr).Copy 'Copying format of last row
                                                                                Rows(Dr + 1).PasteSpecial Paste:=xlPasteFormats 'Pasting format to new row
                                                                                Application.CutCopyMode = False 'Deactivating copy mode



            Application.ScreenUpdating = True
            Else: GoTo G
                End If
            End If

G:

            Sheet3.Activate
                Range(Cells(Ar, 1), Cells(A, 1)).Select
                  Selection.EntireRow.Hidden = False

            Sheet5.Activate
                Range(Cells(Qr, 1), Cells(D, 1)).Select
                Selection.EntireRow.Hidden = False

            Sheet3.Select


            Sheet5.Visible = False
            Cells(Br, "C").Select
            Sheet3.Activate
            ActiveSheet.Protect
            Application.ScreenUpdating = True
            End Sub
2
vbaexcel
The first thing you should do is avoid activating and selecting objects. Prefix your Cells and Range with sheet variable. - JohnyL
You need to understand that, when you perform action like Cells(x,y).Select or Sheet3.Activate macro will show and perform the action. Which causes the flickers. So as said by @JohnyL AVOID those in all possible ways - nishit dey
So, I tried that, it didn't solve the problem, can you help me edit the code to show how it would work? Thanks - NOVICE

2 Answers

0
votes

I'll just do the first bit to give you an idea:

  Sub Add_Observation()
        '
        Application.ScreenUpdating = False
        with Sheet3
            .Unprotect
        End With
        With Sheet5
                Dim Ar As Integer, Br As Integer, A As Integer
                '
                Ar =. Columns("B").Find(What:="S/N", After:=.Range("B26")).Row 'Searching row of "S/N" header
                Br = .Range("B" & Ar).End(xlDown).Row 'Searching last row in EQUIPMENT table
                A = (Br + 4)
                B = (Br + 5)


                Dim Qr As Integer, Dr As Integer, D As Integer, D1 As Integer
                '
                Qr = .Columns("B").Find(What:="S/N", After:=.Range("B16")).Row
                Dr = .Range("B" & Qr).End(xlDown).Row
                D = (Dr + 4)
                D1 = (Dr + 2)

     end With
      With  Sheet3
            'Cells(Dr, "C").Select

        If .Range(.Cells(Ar, 1), .Cells(A, 1)).EntireRow.Hidden = False Then

 with sheet5
     .Rows(Dr + 1).PasteSpecial Paste:=xlPasteFormats 'Pasting format to new row

      Application.CutCopyMode = False 'Deactivating copy mode

 .Rows(D).Insert Shift:=xlDown  'Inserting new row

   .Rows(D1).Insert Shift:=xlDown

  .Cells(Dr + 1, "B") = 'Cells(Dr, "B") + 1 'Adding a sequential Number

  .Rows(Dr).Copy 'Copying format of last row
0
votes

It's kind of a kludge, but try turning screen updating on and off outside your routine by wrapping the routine in another sub.

Delete all the application.screenupdating lines from your code, otherwise they will override the code below.

Public Sub callAdd_Observation()
    Application.ScreenUpdating = False
    Call Add_Observation
    Application.ScreenUpdating = True
End Sub