Screen Updating Difficulty - Any Ideas?

Scotthall1974

New Member
Joined
Mar 15, 2018
Messages
5
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!

Code:
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
 
Last edited by a moderator:

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
You're writing formulae to cells without selecting them, and you should be able to use the same approach with objects. So this:


Code:
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):

Code:
With Sheets("1.0 Gen Info").Shapes("Button").ShapeRange
.IncrementLeft 53
.Fill.ForeColor.RGB = RGB(0,153,0)
End With
 
Last edited:
Upvote 0
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.
 
Upvote 0
No, you don't need to select the button. This is a better version of my code:

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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top