Annoying flicker

[ABabeNChrist]

I have 2 sheets that are identical in appearance but use different names. I use this code below to enter values from active sheet to the Summary and Summary Texas sheet.

Code: Select all

Private Sub CommandButton1_Click()
    ActiveWorkbook.Protect PassWord:="", Structure:=False, Windows:=False
    Sheets("Summary").Unprotect ""
    Sheets("Summary Texas").Unprotect ""

    Sheets("Summary").Range("A1").Value = Range("B23").Value
    Sheets("Summary Texas").Range("A1").Value = Range("B23").Value

    Sheets("Summary").Range("A2").Value = " Date of Inspection: " & Range("E6").Value
    Sheets("Summary Texas").Range("A2").Value = " Date of Inspection: " & Range("E6").Value

    Sheets("Summary").Range("A3").Value = " Inspection Address: " & Range("B30").Value
    Sheets("Summary Texas").Range("A3").Value = " Inspection Address: " & Range("B30").Value

    Sheets("Client_info").Visible = xlSheetHidden

    Sheets("Summary").Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
                              PassWord:="", UserInterfaceOnly:=True
    Sheets("Summary Texas").Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
                                    PassWord:="", UserInterfaceOnly:=True
    ActiveWorkbook.Protect PassWord:="", Structure:=True, Windows:=True

    UserForm2.Show
End Sub

This approach does work but it seems to flicker then goes to userform 2. I understand why it’s flickering but is it possible to eliminate the annoying flicker……
I tried changing some of the code with using this approach with no such luck, any suggestions would be greatly appreciated.

Code: Select all

Dim sht As Worksheet
For Each sht In Sheets(Array("Summary", "Summary Texas"))
   sht.UnProtect Password:=""
   Next sht

For Each sht In Sheets(Array("Summary", "Summary Texas"))
   sht. Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
                                    PassWord:="", UserInterfaceOnly:=True   Next sht

Along with true before and false after code

Code: Select all

    Application.ScreenUpdating = True
    Application.ScreenUpdating = False

[HansV]

You should set ScreenUpdating to False before the code and to True after it, instead of the other way round.

[ABabeNChrist]

Opps, My mistake, that what I meant in my post, I was in a little hurry. False first then true

[HansV]

Do you mean that you actually set ScreenUpdating to False first, but still get the screen flicker?

[ABabeNChrist]

Yes

[HansV]

You'll have to live with it then.

[ABabeNChrist]

You'll have to live with it then.

It wasn’t that of a big deal, just shooting for perfection……lol
I thank You Hans

[StuartR]

On a separate issue. Be very careful with lines like

Code: Select all

Sheets("Summary").Range("A1").Value = Range("B23").Value

You presumably intend that Range("B23") is a reference to a cell on the ActiveWorksheet, but it would be best to put this in explicitly to make it clear.

[ABabeNChrist]

So if my active sheet was Sheet1 then it would be best to use.

Code: Select all

    Sheets("Summary").Range("A1").Value = _
    Sheets("Sheet1").Range("B23").Value

And if I were to refer to 2 different cells, use something like this

Code: Select all

    Sheets("Summary").Range("A1").Value = _
    Sheets("Sheet1").Range("B23").Value & _
    Sheets("Sheet1").Range("B24").Value

[HansV]

Where is your code? In a "standard" module or in the worksheet module of Sheet1?

[ABabeNChrist]

Module of Sheet1

[HansV]

In that case, you can use Me to refer to the sheet. The advantage is that it won't matter if you rename the sheet:

Code: Select all

    Sheets("Summary").Range("A1").Value = _
    Me.Range("B23").Value & _
    Me.Range("B24").Value

[ABabeNChrist]

I have a similar question in regards to range values from other sheets
Let’s say from sheet 1 I select a command button that opens a userform that has checkboxes.
That has this code

Code: Select all

Private Sub CommandButton1_Click()
    Dim blnSelected As Boolean

    ActiveWorkbook.Protect PassWord:="", Structure:=False, Windows:=False
    Sheets("Summary").Unprotect ""

    If CheckBox1.Value = True Then
        blnSelected = True
        Sheets("Summary").Range("A9:A10").EntireRow.Hidden = False
        Sheets("Summary").Range("A9").Value = Range("A7").Value & _
                                              ": " & Range("A10").Value
        Sheets("Summary").Range("A10").Value = " • " & Range("A16").Value
        Sheets("Summary").Range("A10").EntireRow.AutoFit
        Sheets("Summary").Range("A8").EntireRow.Hidden = False

    End If

    If CheckBox2.Value = True Then
        blnSelected = True
        Sheets("Summary").Range("A11:A12").EntireRow.Hidden = False
        Sheets("Summary").Range("A11").Value = Range("A18").Value & _
                                               ": " & Range("A21").Value
        Sheets("Summary").Range("A12").Value = " • " & Range("A27").Value
        Sheets("Summary").Range("A12").EntireRow.AutoFit
        Sheets("Summary").Range("A8").EntireRow.Hidden = False

    End If

    If CheckBox3.Value = True Then
        blnSelected = True
        Sheets("Summary").Range("A13:A14").EntireRow.Hidden = False
        Sheets("Summary").Range("A13").Value = Range("A29").Value & _
                                               ": " & Range("A32").Value
        Sheets("Summary").Range("A14").Value = " • " & Range("A38").Value
        Sheets("Summary").Range("A14").EntireRow.AutoFit
        Sheets("Summary").Range("A8").EntireRow.Hidden = False

    End If

    If CheckBox4.Value = True Then
        blnSelected = True
        Sheets("Summary").Range("A15:A16").EntireRow.Hidden = False
        Sheets("Summary").Range("A15").Value = Range("A40").Value & _
                                               ": " & Range("A43").Value
        Sheets("Summary").Range("A16").Value = " • " & Range("A49").Value
        Sheets("Summary").Range("A16").EntireRow.AutoFit
        Sheets("Summary").Range("A8").EntireRow.Hidden = False

    End If

    Sheets("Summary").Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
                              PassWord:="", UserInterfaceOnly:=True

    ActiveWorkbook.Protect PassWord:="", Structure:=True, Windows:=True

    If blnSelected = True Then
        MsgBox "Your selection(s) have now been added to Report Summary page"
        Unload Me
        Unload UserForm5
    Else
        MsgBox "No check boxes were selected"
    End If
End Sub

Would I then need to refer to Sheet1 as the = value range. it works as it is

[HansV]

If you don't explicitly specify the sheet a range belongs to, Excel will assume that the range is on the active worksheet. So as long as you don't activate another worksheet in your code, it is safe to omit the sheet reference.

[ABabeNChrist]

Thank You Hans and StuartR

[kpark91]

Hi,
I believe you can just prevent the flickers
but the window will be completely locked.

Code: Select all

Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
Private Const WM_SETREDRAW = &HB

Private Declare Function LockWindowUpdate Lib "user32" _
  (ByVal hwndLock As Long) As Long

Sub RunProjectUpdate()

Dim Lastrow As Long, lastrow1 As Long

Application.ScreenUpdating = False

'// Use this for stopping redrawing:
SendMessage Application.hwnd, WM_SETREDRAW, 0&, 0&

'LockWindowUpdate Application.hwnd




'YOUR CODE


'You MUST turn it back on at the end!!!:

SendMessage Application.hwnd, WM_SETREDRAW, 1&, 0&

'LockWindowUpdate ByVal 0&   'simply passing a long value of 0 into the function

End Sub

I don't know how it works but I got it off of a website.

[ABabeNChrist]

I haven’t tried yet but wouldn’t I also need to add, this just before End Sub

Code: Select all

Application.ScreenUpdating = True

[HansV]

I'd try omitting Application.ScreenUpdating = False; the SendMessage line takes its place.

[ABabeNChrist]

Thank you Hans and kpark91
I wasn’t sure, usually when I see Application.ScreenUpdating = True at the beginning of the code I figure that Application.ScreenUpdating = False would follow at the end of code.

[kpark91]

Thanks Hans. I didn't realize that too.
I am gonna take Application.ScreenUpdating out of my library now :P