Screen Updating Difficulty - Any Ideas?

[Scotthall1974]

Hello All!

I am having a tricky time figuring out why I am still getting a screen update in my VBA when I really do not want it. It will just freak out my users and lead to more questions. I have a toggle box switch in VBA that moves, changes colours and executes a sub based on the position of the toggle.

The toggle is on one password protected tab and it updates a formula on another password protected tab depending on the switch location. I have tried various locations for the screenupdating line and no matter where I put it I still get the flashes. Sometimes it takes a few runs of the toggle (VBA does not change - just toggle position) before it does, but eventually after a couple the screen updates.

The code should be unlocking all the tabs needed doing the substitutions and relocking the cells. That seems to be working fine, maybe the code is too jumpy or combersome, I only dabble in it. Any help would be greatly appreciated. Thank you all!

Option Explicit

Sub Include_LNR_Materials()


Application.ScreenUpdating = False


    'Select Button
Worksheets("1.0 Gen Info").Shapes("Button").Select




    ' Change Position, Color & Text
With Selection
.ShapeRange.IncrementLeft 53
.ShapeRange.Fill.ForeColor.RGB = RGB(0, 153, 0)
End With


    'Assign OFF_BUTTON macro after changing position
Worksheets("1.0 Gen Info").Shapes("Button").OnAction = "Exclude_LNR_Materials"


    
    'Macro to Run when switch is ON
Call YES_LNR_COSTS


    'Activate some other cell after completion
Worksheets("1.0 Gen Info").Activate
            Range("J51").Select






End Sub
---------------------
Sub Exclude_LNR_Materials()


Application.ScreenUpdating = False


    'Select Button
Worksheets("1.0 Gen Info").Shapes("Button").Select




    ' Change Position, Color & Text
With Selection
.ShapeRange.IncrementLeft -53
.ShapeRange.Fill.ForeColor.RGB = RGB(255, 0, 0)
End With


    'Assign ON_BUTTON macro after changing position
Worksheets("1.0 Gen Info").Shapes("Button").OnAction = "Include_LNR_Materials"


    'Macro to Run when switch is OFF
Call NO_LNR_COSTS


    'Activate some other cell after completion
Worksheets("1.0 Gen Info").Activate
            Range("J51").Select
            


End Sub
-----------------------
Sub YES_LNR_COSTS()


Dim Password As String
Password = "gosislaw"
    
    Worksheets("Summary").Unprotect Password:=Password
    Worksheets("1.0 Gen Info").Unprotect Password:=Password
    
        
        Worksheets("1.0 Gen Info").Range("K51").Formula = "='3.0 Material'!Z8-'3.0 Material'!V4"
        Worksheets("1.0 Gen Info").Range("K52").Clear
        Worksheets("1.0 Gen Info").Range("K51:K52").Font.Color = RGB(255, 255, 255)
        
        Worksheets("Summary").Range("I8").Formula = "='3.0 Material'!Z8"
    
    
    Worksheets("1.0 Gen Info").Protect Password:=Password
    Worksheets("Summary").Protect Password:=Password


Application.ScreenUpdating = True


'TESTING


End Sub
------------------------
Sub NO_LNR_COSTS()


Dim Password As String
Password = "gosislaw"
    
    Worksheets("Summary").Unprotect Password:=Password
    Worksheets("1.0 Gen Info").Unprotect Password:=Password
        
        
        Worksheets("1.0 Gen Info").Range("K52").Formula = "='3.0 Material'!Z8"
        Worksheets("1.0 Gen Info").Range("K51").Clear
        Worksheets("1.0 Gen Info").Range("K51:K52").Font.Color = RGB(255, 255, 255)
    
        Worksheets("Summary").Range("I8").Formula = "='3.0 Material'!Z8-'3.0 Material'!V4"


        
    Worksheets("1.0 Gen Info").Protect Password:=Password
    Worksheets("Summary").Protect Password:=Password
     
Application.ScreenUpdating = True
  
'TESTING
-----------------------
End Sub

 

[Roderick_E]

I imagine your code is calling different routines/subs as it progresses? If so, then set the screen updating false and true ONLY on the initial/first routine to fire. You're probably turning it back on somewhere in the middle of the calls

 

[njimack]

You don't need to select sheets, ranges or objects to perform actions on them.

 

[Scotthall1974]

I just gave that another try. I disabled updates at the beginning of the sub's for each toggle that actually changes the cells, but I still get the flash. It appears to be jumping between the two tabs, one with the toggle and the other where the formula is being pasted in.

 

[Scotthall1974]

Neil, can you explain which part of the code you think is not right or could be improved? Other than the updating it does work, maybe not the best though.

 

[njimack]

You're writing formulae to cells without selecting them, and you should be able to use the same approach with objects. So this:

Worksheets("1.0 Gen Info").Shapes("Button").Select

With Selection
.ShapeRange.IncrementLeft 53
.ShapeRange.Fill.ForeColor.RGB = RGB(0, 153, 0)
End With

becomes this (untested):

With Sheets("1.0 Gen Info").Shapes("Button").ShapeRange
.IncrementLeft 53
.Fill.ForeColor.RGB = RGB(0,153,0)
End With

 

[Scotthall1974]

Would I still not need is as another line of code as it executes the shape movements and changes upon the selection? Define the button, then what happens when the button is selected.

 

[njimack]

No, you don't need to select the button. This is a better version of my code:

With Sheets("1.0 Gen Info").Shapes("Button")
With .ShapeRange
.IncrementLeft 53
.Fill.ForeColor.RGB = RGB(0,153,0)
End With
.OnAction = "Include_LNR_Materials"
End With

 

[Scotthall1974]

At the first ShapeRange call it throws a "Object doesn't support this property or method" error.