Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Screen Updating = False not working

Status
Not open for further replies.
Hacktastic

Hacktastic

Technical User
Feb 27, 2007
54
US
Hi team,

I have vba sub that gets called from a change in a value of a cell:

ie:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Target.Address = "$C$5" Then
nonupdating
 BU
...
end if

Where nonupdating is :
Code: 
Option Explicit
Public Sub nonupdating()
With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
    End With
End Sub

and BU is
Code: 
Option Explicit

Sub BU()
' BU Macro
' Macro recorded 10/19/2010 by Charles Grayson
'
nonupdating
    Unprotect_Sheet
    Range("D1").Select
    Sheets("Consolidated Data").Select
       With Application
        .Calculation = xlManual
        .MaxChange = 0.001
    End With
    ActiveWorkbook.PrecisionAsDisplayed = False
    Range("J2").Select
    Selection.AutoFilter Field:=9, Criteria1:="YES"
    Columns("G:G").Select
    Selection.Copy
    Sheets("User Interface").Select
    Columns("N:N").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWorkbook.PrecisionAsDisplayed = False
    Sheets("Consolidated Data").Select
    Application.CutCopyMode = False
    ActiveSheet.ShowAllData
    With Application
        .Calculation = xlAutomatic
        .MaxChange = 0.001
        Sheets("User Interface").Select
    Range("C7:C12").Select
    Range("C12").Activate
    Selection.ClearContents
    Range("B2").Select
    End With
    nonupdating
    Sheets("Consol Data 2").Select
    Selection.AutoFilter Field:=4, Criteria1:="<>"
    Selection.AutoFilter Field:=1, Criteria1:="=1", Operator:=xlAnd
    Columns("H:L").Select
    Selection.Copy
    nonupdating
    Sheets("User Interface").Select
    Range("Z1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Consol Data 2").Select
    Selection.AutoFilter Field:=1
    Selection.AutoFilter Field:=1, Criteria1:="=2", Operator:=xlAnd
    Columns("H:L").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("User Interface").Select
    Range("AE1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
       Sheets("Consol Data 2").Select
    Selection.AutoFilter Field:=1, Criteria1:="=3", Operator:=xlAnd
    Columns("H:L").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("User Interface").Select
    Range("AJ1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Consol Data 2").Select
    Selection.AutoFilter Field:=1, Criteria1:="=4", Operator:=xlAnd
    Columns("H:L").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("User Interface").Select
    Range("AO1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Consol Data 2").Select
    Selection.AutoFilter Field:=1, Criteria1:="=5", Operator:=xlAnd
    Columns("H:L").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("User Interface").Select
    Range("AT1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Consol Data 2").Select
    Selection.AutoFilter Field:=1, Criteria1:="=6", Operator:=xlAnd
    Columns("H:L").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("User Interface").Select
    Range("AY1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Consol Data 2").Select
    Selection.AutoFilter Field:=1, Criteria1:="=7", Operator:=xlAnd
    Columns("H:L").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("User Interface").Select
    Range("BD1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Consol Data 2").Select
    Selection.AutoFilter Field:=1, Criteria1:="=8", Operator:=xlAnd
    Columns("H:L").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("User Interface").Select
    Range("BI1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
      Sheets("Consol Data 2").Select
    Selection.AutoFilter Field:=1, Criteria1:="=9", Operator:=xlAnd
    Columns("H:L").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("User Interface").Select
    Range("BN1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
       Sheets("Consol Data 2").Select
    Selection.AutoFilter Field:=1, Criteria1:="=10", Operator:=xlAnd
    Columns("H:L").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("User Interface").Select
    Range("BS1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Consol Data 2").Select
    Selection.AutoFilter Field:=1, Criteria1:="=11", Operator:=xlAnd
    Columns("H:L").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("User Interface").Select
    Range("BX1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Consol Data 2").Select
    Selection.AutoFilter Field:=1, Criteria1:="=12", Operator:=xlAnd
    Columns("H:L").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("User Interface").Select
    Range("CC1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Consol Data 2").Select
    Selection.AutoFilter Field:=1, Criteria1:="=13", Operator:=xlAnd
    Columns("H:L").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("User Interface").Select
    Range("CH1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
      Sheets("Consol Data 2").Select
    Selection.AutoFilter Field:=1, Criteria1:="=14", Operator:=xlAnd
    Columns("H:L").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("User Interface").Select
    Range("CM1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
     Sheets("Consol Data 2").Select
    Selection.AutoFilter Field:=1, Criteria1:="=15", Operator:=xlAnd
    Columns("H:L").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("User Interface").Select
    Range("CR1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Consol Data 2").Select
    Application.CutCopyMode = False
    ActiveSheet.ShowAllData
    Sheets("User Interface").Select
    Range("B2").Select
Protect_Sheet
End Sub

the screen updating portion for BU is not working and it giving me a headache switching between sheets. the Screen Updating sub i created is not working. any advice would be greatly appreciated.

 
Sort by date Sort by votes



Please explain exactly what you mean by 'not working'.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Hi skip

Thank you for your time.

Screen updatingisnotworking means excel shows me the activity in sub BU. Aka tab switching, column selecting...etc
 
Upvote 0 Downvote


Can't tell, but why are you using the Select method to begin with? Very ineffiicient!

Example...
Code: 
Sub BU()
' BU Macro
' Macro recorded 10/19/2010 by Charles Grayson
'
nonupdating
    Unprotect_Sheet
'    Range("D1").Select   this does NOTHING
    With Application
        .Calculation = xlManual
        .MaxChange = 0.001
    End With

    ActiveWorkbook.PrecisionAsDisplayed = False

    Sheets("Consolidated Data").Range("J2").AutoFilter Field:=9, Criteria1:="YES"

    Sheets("Consolidated Data").Columns("G:G").Copy

    Sheets("User Interface").Columns("N:N").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    ActiveWorkbook.PrecisionAsDisplayed = False

    Application.CutCopyMode = False
    Sheets("Consolidated Data").ShowAllData

    With Application
       .Calculation = xlAutomatic
       .MaxChange = 0.001
    End With

    Sheets("User Interface").Range("C7:C12").ClearContents
'.....


Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
I would highly suggest... well quite a few things.

But for one, I don't see any beneficial reason to 1) create a subroutine to turn off ScreenUpdating and DisplayAlerts. or 2) call it anywhere near as often as you are.

Just set it to False in the beginning of BU, and put it back to True at the end.

You... really should get familiar with loops, and not using the Select method, as Skip suggests.
 
Upvote 0 Downvote
Thanks for your suggetion Gruuu,

this code was inherited to me, and i tried placing the screenupdating and display alerts in the beginning of the sub with no avail.

the above code to my guess was created using Excel's macro recorder. I wasn't scoped as a programmer to fix his code, just to make sure the screen updating doesnt turn on.

the problem is that somewhere somehow the screenupdating turns itself back ON somewhere in the code. it is suggested that that happens when Excel gains control over the workbook/changing variabiles.

but thats just one stab at it.

 
Upvote 0 Downvote
Fair enough, it's just a lot easier to find out where things go wrong when you can actually see what's going on.

Indeed, you might spend far less time creating an elegant script, and fixing the problem, then slogging through the ...dare I say crap?... that was left to you.

That's not usually the case, I will concede. However I maintain that the time would be far better spent.
 
Upvote 0 Downvote
IMO, i would simply modify the SQL ODBC connection for a dynamic datapull, but that's just me...


perhaps i should take another look at it. sup up the code like you guys say...test out if cleaner code makes the screen updating sticky.

Tek tips Rulez
 
Upvote 0 Downvote


Use Edit > Find - IN ENTIRE PROJECT to search for ScreenUpdating in order to find where it is turned on.

Basically you should have ONE statement that turns it off and ONE statement that turns it on.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Solved
Combine two working macros into one for MS Word 1
Replies
7
Views
828
strongm
strongm
Moss100
  • Locked
  • Question
Updating Outlook Contacts from Access
Replies
0
Views
271
Moss100
Moss100
morechocolate
  • Locked
  • Question
run-tune error 3704 operation is not allowed when the object is codes: I don't think so.
Replies
7
Views
910
morechocolate
morechocolate
MattGreer
  • Locked
  • Question
Outlook 365 - Screen Not Updating During Code Execution 1
Replies
2
Views
133
MattGreer
MattGreer
renigar
  • Locked
  • Question
Excel 2010 macro not working in Excel 365
Replies
19
Views
377
RobertPhillips
RobertPhillips

Part and Inventory Search

Sponsor

Back
Top