vba to stop screen flickering when code running

[cjcass]

Hi,
I have the code below with the usual Application.ScreenUpdating incorporated but the screen still flashes/flickers when the code runs - everything (charts/tables etc.) momentarily disappear and re-appear when it runs so it doesn't look very slick. The page has a couple of charts reading from other sheets, some tables with formulas, text boxes reading from other sheets and some combo boxes being used as dropdowns. I'm using Excel 2016.
Any thoughts?

Private Sub ComboBox7_Change()


On Error GoTo errHandle:
    
    Application.ScreenUpdating = False
    
    Sheet8.Unprotect Password:="pass1"
    Sheet17.Visible = True
    Call Macro41
    Sheet17.Visible = xlVeryHidden
    Sheet8.Select
    Range("R22").Select
    Sheet8.Protect Password:="pass1"


    Application.ScreenUpdating = True
    
errHandle:


End Sub

 

[louisH]

Hi.

Here are some others tricks to ease processing :

Application.Calculation = xlCalculationManual

Then

Application.Calculation = xlCalculationAutomatic

Application.EnableEvents = False

Then

Application.EnableEvents = True

You might also check if your Macro41 doesn't have any Application.ScreenUpdating too which will momentarily re-activate it, allowing the screen to update.

 

[cjcass]

Hello and thanks for your reply.
I tried adding the Events and Calculation codes but it made no difference, also checked Macro41 and that doesn't have any Application.ScreenUpdating.
I then tried calling the code from a separate subroutine which used Application.ScreenUpdating but to no avail.
I think the issue might be the fact that protecting/unprotecting a sheet activates the sheet, despite the setting for Application.ScreenUpdating and this may also be applicable to the code hiding/unhiding a sheet?
Any further thoughts??

 

[cjcass]

Have read in another forum that you should aim to use VBA code without using Select/Selection/Activate as when you set the focus back to Excel (via Select etc.) ScreenUpdating it set back to 'True'.
How would I adapt the code above to remove any 'Select' commands? (Macro41 selects ranges etc. also)

 

[louisH]

Have read in another forum that you should aim to use VBA code without using Select/Selection/Activate as when you set the focus back to Excel (via Select etc.) ScreenUpdating it set back to 'True'.
How would I adapt the code above to remove any 'Select' commands? (Macro41 selects ranges etc. also)

You are right, there's always a better solution than using the Select command which can have side effects.
Reading your code, i don't see why you are using the select command as you don't do anything with your selection.

Just try to delete the lines containing ".Select" and run the macro but if your ScreenUpdating is set to false it shouldn't solve your problem.

For my personal knowledge, i'm not familiar with this reference to your sheets :

Sheet8.Unprotect...

What does Sheet8 refer to ? I don't see any declaration of Sheet8 and Sheet17 in your code. I would have understand Sheets("SheetName") to refer to a sheet, or

Set Sheet8 = Worksheets("SheetName")

so this confuses me.

What does Macro41 do, and does your code do Something else ? Do you have any Worksheets module ? (e.g. Worksheets_Change) ?

 

[vcoolio]

@ LouisH,

The OP is referring to the sheet code not the sheet name. If you look at the sheet list in the VB Editor, you'll notice that each sheet has a name in parentheses and the sheet code directly to the left. It is a very safe way to refer to worksheets as the sheet name could possibly be changed in future for any reason and if this does happen, the code is dead. Using the sheet code will ensure that the code will always work.

i hope that this helps.

Cheerio,
vcoolio.

 

[cjcass]

Hi,
Sheet8 is the worksheet that ComboBox7 resides in, along with the other items decribed above, charts etc.
Sheet17 is the sheet that Macro41 works in, the code is below...
When the selection in ComboBox7 changes, Macro41 changes cells values in Sheet17, a TextBox (ActiveX Control) located below ComboBox7 in Sheet8 reads from the new cell value in Sheet17.
Hope this helps.

Sub Macro41()
'
' Macro41 Macro
'


'
    
    Sheet17.Select
    
    Dim lrow As Long
    lrow = Cells(Cells.Rows.Count, "C").End(xlUp).Row
    
    
    Range("AH5:AL5").Select
    Selection.Copy
    Range("AH6:AL" & lrow).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.Replace What:="zz", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    
    Range("AH6:AI" & lrow).Select
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range( _
        "AH6:AH" & lrow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.ActiveSheet.Sort
        .SetRange Range("AH6:AI" & lrow)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    
    Range("AK6:AL" & lrow).Select
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range( _
        "AK6:AK" & lrow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.ActiveSheet.Sort
        .SetRange Range("AK6:AL" & lrow)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("AH1").Select
    
For j = 6 To 1000
If Cells(j, 35) = "" Then GoTo 100
myconcat = myconcat & Cells(j, 35)
Next j
100 Cells(1, 35) = myconcat
myconcat = ""
For j = 6 To 1000
If Cells(j, 38) = "" Then GoTo 1000
myconcat = myconcat & Cells(j, 38)
Next j
1000 Cells(1, 38) = myconcat
   
     
    
End Sub

 

[louisH]

@ LouisH,

The OP is referring to the sheet code not the sheet name. If you look at the sheet list in the VB Editor, you'll notice that each sheet has a name in parentheses and the sheet code directly to the left. It is a very safe way to refer to worksheets as the sheet name could possibly be changed in future for any reason and if this does happen, the code is dead. Using the sheet code will ensure that the code will always work.

i hope that this helps.

Cheerio,
vcoolio.

Thank you for that information I did not know that .

 

[vcoolio]

No worries LouisH. We can all help and learn from each other.

Cheerio,
vcoolio.

 

[louisH]

Hi,
Sheet8 is the worksheet that ComboBox7 resides in, along with the other items decribed above, charts etc.
Sheet17 is the sheet that Macro41 works in, the code is below...
When the selection in ComboBox7 changes, Macro41 changes cells values in Sheet17, a TextBox (ActiveX Control) located below ComboBox7 in Sheet8 reads from the new cell value in Sheet17.
Hope this helps.

After further experimentations, I discovered that you don't even need the sheet to be visible to operate on it.
I tried to modify your code to remove any Select/Activate command + in my opinion you don't need to unprotect the Sheet8 for the TextBox to update.
Try the followings :

Private Sub ComboBox7_Change()


On Error GoTo errHandle:
    
    Application.ScreenUpdating = False
    
    'Sheet8.Unprotect Password:="pass1"
    Call Macro41
    'Sheet8.Protect Password:="pass1"


    Application.ScreenUpdating = True
    
errHandle:


End Sub

Sub Macro41()
    
    Dim ws As Worksheet
    Set ws = Sheet17
    
    Dim lrow As Long
    lrow = ws.Cells(ws.Cells.Rows.Count, "C").End(xlUp).Row
    
    Dim CopyDestination As Range
    Set CopyDestination = ws.Range("AH6:AL" & lrow)
    
    ws.Range("AH5:AL5").Copy
    CopyDestination.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    
    CopyDestination.Copy
    CopyDestination.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    CopyDestination.Replace What:="zz", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    ws.Sort.SortFields.Clear
    ws.Sort.SortFields.Add Key:=ws.Range( _
        "AH6:AH" & lrow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ws.Sort
        .SetRange ws.Range("AH6:AI" & lrow)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    ws.Sort.SortFields.Clear
    ws.Sort.SortFields.Add Key:=ws.Range( _
        "AK6:AK" & lrow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ws.Sort
        .SetRange ws.Range("AK6:AL" & lrow)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    For j = 6 To 1000
    If ws.Cells(j, 35) = "" Then GoTo 100
    myconcat = myconcat & ws.Cells(j, 35)
    Next j
100     ws.Cells(1, 35) = myconcat
    myconcat = ""
    For j = 6 To 1000
    If ws.Cells(j, 38) = "" Then GoTo 1000
    myconcat = myconcat & ws.Cells(j, 38)
    Next j
1000     ws.Cells(1, 38) = myconcat
   
     
End Sub