Application.ScreenUpdating parameter doesn't work

[grizzlybear]

Hi,

I am trying to run a macro in the background. I have added the Application.ScreenUpdating = False (and True at the end) to my code but no luck. Any ideas?

 

[goldfish]

What do you mean "Run a macro in the background", please describe this in great detail including a description of what background actions you wish it to be doing. Perhaps post the macro as well if available.

Turning ScreenUpdating off will make it so you won't see any of the changes the macro makes until it turns on again. It essentially freezes excel on its current screen. It is used for speeding up macros as well as avoiding making the user watch all of a macro's dirty work while it runs. You will not be able to interact with excel while the ScreenUpdating is false, which is probably not what you are looking for.

So please describe the effect you wish and I will see if I can help you create it.
~Gold Fish

 

[Smitty]

How about posting your code?

It's hard to tell without seeing that...

Smitty

 

[tusharm]

There is almost never a need to turn off ScreenUpdating. It is typically a sign of code with Activate and Select statements, the vast majority of which can be eliminated. For a place to start see
Beyond the macro recorder
http://www.tushar-mehta.com/excel/vba/beyond_the_macro_recorder/index.htm

Hi,

I am trying to run a macro in the background. I have added the Application.ScreenUpdating = False (and True at the end) to my code but no luck. Any ideas?

 

[grizzlybear]

Thanks for the reply.

I have a button which fires a macro. When I click the button, the macro selects the sheet on which the functionality runs. The focus does not return to the original sheet. Here is the code:

Application.ScreenUpdating = False

 Sheets("Truck Production Hourly Data").Select
    
    Range("B24").Select
       
    Do Until Selection.Value = ""
      
        Selection.Offset(0, -1).Value = Mid(Selection.Value, 2, 2)
        Selection.Offset(0, -1).Select
        
      
            If Selection.Value = Range("AE2").Value And Range("AB2").Value = "EAST" Then
                Selection.Offset(0, 2) = Range("AD2").Value
            End If
        
       
        Selection.Offset(1, 1).Select
        
        Loop
        

    Range("B24").Select
    
        Do Until Selection.Value = ""
     
        Selection.Offset(0, -1).Select
              
            If Selection.Value = Range("AE2").Value And Range("AB2").Value = "WEST" Then
                Selection.Offset(0, 3) = Range("AD2").Value
            End If
        
        Selection.Offset(1, 1).Select
    
        Loop
    
    Range("B24").Select
    
        Do Until Selection.Value = ""
      
         Selection.Offset(0, -1).Select
              
            If Selection.Value = Range("AE2").Value And Range("AB2").Value = "NORTH" Then
                
                Selection.Offset(0, 4) = Range("AD2").Value
            
            End If
        
        Selection.Offset(1, 1).Select
        
        Loop
        
        Range("A24:A43").Value = ""
      
       Application.ScreenUpdating = False
          
    End
    
End Sub

I have another macros that run in the background, presumably due to the parameter working. I can't see any difference. However, I added a watch to the parameter. It changed to "True" as expected, but never changed back to false after the code ran. With my other macro the parameter turns on and off as expected.

EDIT: added code tags - Smitty

 

[Boller]

There is almost never a need to turn off ScreenUpdating. It is typically a sign of code with Activate and Select statements, the vast majority of which can be eliminated

Oh? What about macros that have no Activate and Select Statements but do a lot of formatting (e.g. inserting/deleting of cells/rows/columns, etc.)

 

[goldfish]

You still haven't explained what it means by having it work in the background, but I can only assume you mean not having to actually switch to page that the macro is running on (?). This can be done by not using Select or Selection in your macro. Select and Selection are poor coding techniques as almost everything can be done without them, and they make the code take longer to run.

Application.ScreenUpdating = False 
Dim RNG as range
With Sheets("Truck Production Hourly Data")
Set RNG = .Range("B24")
Do Until RNG.Value = ""
RNG.Offset(0, -1).Value = Mid(RNG.Value, 2, 2)
Set RNG = RNG.Offset(0, -1)
If RNG.Value = .Range("AE2").Value And .Range("AB2").Value = "EAST" Then
RNG.Offset(0, 2) = .Range("AD2").Value
End If
Set RNG = RNG.Offset(1, 1)
Loop
Set RNG = .Range("B24")
Do Until RNG.Value = ""
Set RNG = RNG.Offset(0, -1)
If RNG.Value = .Range("AE2").Value And .Range("AB2").Value = "WEST" Then
RNG.Offset(0, 3) = .Range("AD2").Value
End If
Set RNG =  RNG.Offset(1, 1)
Loop
'And your last loop
Application.ScreenUpdating = False

There are yet better ways of writing this code, but keeping your form much the same, these are some basic ways of programing with using Select and Selection.

But If your only goal is to have the macro stay on the sheet it was run from then you can do the following

Dim wksheet as worksheet
Application.ScreenUpdating = False 
Set wksheet = ActiveSheet
'-------------------------------
'Put the rest of your code here
Sheets("Truck Production Hourly Data").Select   
Range("B24").Select    
Do Until Selection.Value = ""     
Selection.Offset(0, -1).Value = Mid(Selection.Value, 2, 2)
Selection.Offset(0, -1).Select 
'......

'-------------------------------
wksheet.Select
Application.ScreenUpdating = True

First store the name of the current worksheet, or if you know its going to be the same everytime, you can just write

Sheets("Sheet with Macro Buttons").Select

Right before you turn ScreenUpdating back on, where "Sheet with Macro Buttons" is the name of the sheet you started on.

~Gold Fish

 

[grizzlybear]

Hey Goldfish, thankyou, that did it. I tried both subs and they both worked. But I do prefer the variable to Select and Selection. My VB experience is approaching 1 week, but a good lesson for me here is to make things happen via code rather than hope the code makes things happen.

Cheers
GBear

 

[goldfish]

Its great when people want to learn more! I've rewritten your code in a much better way so here is some (hopefully) mental candy:

Application.ScreenUpdating = False 
Dim ColumnOffset as Long
Dim RNG as Range

'This line is not efficient as is, because excel works better
'when you specify what kinds of variables you plan to use.
'If these values are integers use
'Dim CheckValue as Long, SetValue as Long
'If these values are decimals use
'Dim CheckValue as Double, SetValue as Double
'If they are text then use
'Dim CheckValue as String, SetValue as String
'But for now this will work:
Dim CheckValue, SetValue

'These values are used many times in the loop (once per iteration).  
'For such values it is better to just store them as variables
CheckValue = Sheets("Truck Production Hourly Data").Range("AE2").Value
SetValue = Sheets("Truck Production Hourly Data").Range("AD2").Value

'Since the only difference between your 3 loops in the Offset amount,
'Lets just set the value here, so we don't have to go through the loop 3 times
'Since in your code it travels through 3 loops no matter what.

' Just in case this value could be other things set ColumnOffset to 0
'Which we will use later
Select Case Sheets("Truck Production Hourly Data").Range("AB2").Value
Case "EAST"
    ColumnOffset = 1
Case "WEST"
    ColumnOffset = 2
Case "NORTH"
    ColumnOffset = 3
Case Else
    ColumnOffset = 0
End Select

'Here we are! if ColumnOffset IS 0 (meaning AB2 is not EAST, WEST, nor NORTH)
'Then this code is not run
If ColumnOffset > 0 Then
    Set RNG as Sheets("Truck Production Hourly Data").Range("B24")
    Do Until RNG.Value = "" 
	'I'm not sure if you are using column A as a placeholder for a value
	'Or if you actually want the text to appear there
        'Add this line back in if it is needed
        'RNG.Offset(0, -1).Value = Mid(RNG.Value, 2, 2)
	'But this check will still work even without this set
            If Mid(RNG.Value, 2, 2) = CheckValue Then 
                RNG.Offset(0, ColumnOffset) = SetValue
            End If 
	'Move to the next one.  It is only nessasary to move once per loop
	'Where before you moved once to the left and then diagonal par each loop
        Set RNG = RNG.Offset(1, 0)
     Loop 
End If
'No need to select the original sheet since you never changed sheets in the first place!
Application.ScreenUpdating = False

it is heavy in the comments (lines with a ' in front are not run) to let you know what is going on.

HTH,
~Gold Fish

 

[grizzlybear]

Goldfish
Thanks for the follow up. Unfortunately, this code won't update the table. The problem is this line
"If Mid(RNG.Value, 2, 2) = CheckValue"
I created a variable - TestValue, and added the following to see what was happening with the "Mid(RNG.Value, 2, 2) " code

It now looks like this
If ColumnOffset > 0 Then
Set RNG = Sheets("Sheet1").Range("B4")
TestValue = Mid(RNG.Value, 2, 2)
Do Until RNG.Value = ""
'I'm not sure if you are using column A as a placeholder for a value
'Or if you actually want the text to appear there
'Add this line back in if it is needed
'RNG.Offset(0, -1).Value = Mid(RNG, 2, 2)
'But this check will still work even without this set
If TestValue = CheckValue Then
RNG.Offset(0, ColumnOffset) = SetValue
End If
'Move to the next one. It is only nessasary to move once per loop
'Where before you moved once to the left and then diagonal par each loop
Set RNG = RNG.Offset(1, 0)
Loop

TestValue changes on the first iteration to the value "10", then holds that value until the code ends. If you want to try it yourself, I have the following list R10, R12, R14, R16........R82, R84 in column B. I have the following in a row (X2:AE2)

1/8/07 10:55 AM, TKD6580, SHD0024, 46N16_LFULLBOT, EAST, 17, 43, 46

I have been going over it for a few hrs now and can't see why it won't work. (I am assuming it's something simple...)

Cheers
GB