What links a click/tap position to select an object on screen?

Screen flash with macros

• QUESTION: I have a workbook of around 80 sheets and everything works perfect until I protect all sheets. Once this is done and I use any of the data validation boxes which trigger the macros. I get screen flashing of the macros running. I do not see the page change just the data that is being changed on all the other sheets flashes on the current sheet I am on. Is there any way to fix this? I do have Application.ScreenUpdating = True and false in all macros. ANSWER: Application.screenupdating should handle it, but possibly it is being set too late? Also, what are the macros actually doing ¨C if they are changing sheets this would give a flash, but there is no need for a macro to do this as it can reference the sheet without activating it ¨C seeing the actual macro may shed some light ¨C if it helps, my email is [email protected] ---------- FOLLOW-UP ---------- QUESTION: Here is three of the macros all in one module. (Not sure if this may be causing it as well.)It was done by performing the actual macro in record mode. I have one macro for each year. Again everything works great until I protect the sheets. Then I still stay on the sheet with the data validation dropdown box which triggers the macro, but the screen shows all of the data cut and pasting on top of eachother in the feild of the same sheet. Here is one macro instead of the three mentioned above. they all are basically doing the same thing, cutting and pasting into the different sheets for the different months of the year. Sub ReplaceData1998() Application.ScreenUpdating = False ' ReplaceData1998 Macro ' Replaces data from Weekly View Data to each month¡¯s weekly view sheet by year and is triggered by drop down menu on the Jan day candle sheet. This macro is for the year 1998 starting with C9 on the Weekly View data! ' ' Keyboard Shortcut: Ctrl+Shift+B ' Range("D2").Select ActiveWindow.ScrollWorkbookTabs Position:=xlLast Sheets("Weekly view data").Select Range("C5:S57").Select Selection.Copy ActiveWindow.ScrollWorkbookTabs Position:=xlFirst Sheets("Jan Weekly View").Select Range("B5").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False ActiveWindow.SmallScroll Down:=-18 Range("B5").Select ActiveWindow.ScrollWorkbookTabs Position:=xlLast Sheets("Weekly view data").Select ActiveWindow.SmallScroll Down:=12 Range("C60:S101").Select Application.CutCopyMode = False Selection.Copy Application.Goto Reference:="FebWeekly" Range("B5").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False ActiveWindow.SmallScroll Down:=33 Range("B53:R57").Select Application.CutCopyMode = False Selection.ClearContents Range("G49:L49").Select Selection.ClearContents ActiveWindow.SmallScroll Down:=-51 Range("B5").Select ActiveWindow.ScrollWorkbookTabs Position:=xlLast Sheets("Weekly view data").Select ActiveWindow.SmallScroll Down:=18 Range("C104:S156").Select Selection.Copy Application.Goto Reference:="MarWeekly" Range("B5").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False ActiveWindow.SmallScroll Down:=-18 Range("B5").Select ActiveWindow.ScrollWorkbookTabs Position:=xlLast Sheets("Weekly view data").Select ActiveWindow.SmallScroll Down:=15 Range("C148:S200").Select Application.CutCopyMode = False Selection.Copy Application.Goto Reference:="AprWeekly" Range("B5").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False ActiveWindow.SmallScroll Down:=-6 Range("B5").Select ActiveWindow.ScrollWorkbookTabs Position:=xlLast Sheets("Weekly view data").Select ActiveWindow.SmallScroll Down:=12 Range("C192:S244").Select Application.CutCopyMode = False Selection.Copy Application.Goto Reference:="MayWeekly" Range("B5").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False ActiveWindow.SmallScroll Down:=-21 Range("B5").Select ActiveWindow.ScrollWorkbookTabs Position:=xlLast Sheets("Weekly view data").Select ActiveWindow.SmallScroll Down:=18 Range("C247:S299").Select Application.CutCopyMode = False Selection.Copy Application.Goto Reference:="JunWeekly" Range("B5").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False ActiveWindow.SmallScroll Down:=-9 Range("B5").Select ActiveWindow.ScrollWorkbookTabs Position:=xlLast Sheets("Weekly view data").Select ActiveWindow.SmallScroll Down:=12 Range("C291:S343").Select Application.CutCopyMode = False Selection.Copy Application.Goto Reference:="JulWeekly" Range("B5").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False ActiveWindow.SmallScroll Down:=-9 Range("B5").Select ActiveWindow.ScrollWorkbookTabs Position:=xlLast Sheets("Weekly view data").Select ActiveWindow.SmallScroll Down:=18 Range("C346:S398").Select Application.CutCopyMode = False Selection.Copy Application.Goto Reference:="AugWeekly" Range("B5").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False ActiveWindow.SmallScroll Down:=-12 Range("B5").Select ActiveWindow.ScrollWorkbookTabs Position:=xlLast Sheets("Weekly view data").Select ActiveWindow.SmallScroll Down:=18 Range("C390:S442").Select Application.CutCopyMode = False Selection.Copy Application.Goto Reference:="SepWeekly" Range("B5").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False ActiveWindow.SmallScroll Down:=0 Range("B5").Select ActiveWindow.ScrollWorkbookTabs Position:=xlLast Sheets("Weekly view data").Select ActiveWindow.SmallScroll Down:=15 Range("C434:S486").Select Application.CutCopyMode = False Selection.Copy Application.Goto Reference:="OctWeekly" Range("B5").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False ActiveWindow.SmallScroll Down:=-15 Range("B5").Select ActiveWindow.ScrollWorkbookTabs Position:=xlLast Sheets("Weekly view data").Select ActiveWindow.SmallScroll Down:=24 Range("C489:S541").Select Application.CutCopyMode = False Selection.Copy Application.Goto Reference:="NovWeekly" Range("B5").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False ActiveWindow.SmallScroll Down:=-6 Range("B5").Select ActiveWindow.ScrollWorkbookTabs Position:=xlLast Sheets("Weekly view data").Select ActiveWindow.SmallScroll Down:=21 Range("C533:S585").Select Application.CutCopyMode = False Selection.Copy Application.Goto Reference:="DecWeekly" Range("B5").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False ActiveWindow.SmallScroll Down:=-3 Range("B5").Select ActiveWindow.ScrollWorkbookTabs Position:=xlLast Sheets("First & Last Weeks").Select ActiveWindow.SmallScroll Down:=-6 Range("B2:R10").Select Application.CutCopyMode = False Selection.Copy ActiveWindow.ScrollWorkbookTabs Position:=xlFirst Sheets("Jan Weekly View").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Range("B5").Select ActiveWindow.ScrollWorkbookTabs Position:=xlLast Sheets("First & Last Weeks").Select Range("B12:R20").Select Application.CutCopyMode = False Selection.Copy Application.Goto Reference:="DecWeekly" ActiveWindow.SmallScroll Down:=30 Range("B49").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False ActiveWindow.SmallScroll Down:=-48 Range("B5").Select ActiveWindow.ScrollWorkbookTabs Position:=xlLast Sheets("First & Last Weeks").Select ActiveWindow.SmallScroll Down:=-9 Range("B2").Select Application.CutCopyMode = False Sheets("Weekly view data").Select ActiveWindow.ScrollRow = 555 ActiveWindow.ScrollRow = 553 ActiveWindow.ScrollRow = 551 ActiveWindow.ScrollRow = 543 ActiveWindow.ScrollRow = 539 ActiveWindow.ScrollRow = 536 ActiveWindow.ScrollRow = 532 ActiveWindow.ScrollRow = 526 ActiveWindow.ScrollRow = 521 ActiveWindow.ScrollRow = 517 ActiveWindow.ScrollRow = 510 ActiveWindow.ScrollRow = 505 ActiveWindow.ScrollRow = 500 ActiveWindow.ScrollRow = 494 ActiveWindow.ScrollRow = 488 ActiveWindow.ScrollRow = 481 ActiveWindow.ScrollRow = 476 ActiveWindow.ScrollRow = 469 ActiveWindow.ScrollRow = 465 ActiveWindow.ScrollRow = 456 ActiveWindow.ScrollRow = 448 ActiveWindow.ScrollRow = 440 ActiveWindow.ScrollRow = 432 ActiveWindow.ScrollRow = 423 ActiveWindow.ScrollRow = 415 ActiveWindow.ScrollRow = 404 ActiveWindow.ScrollRow = 395 ActiveWindow.ScrollRow = 385 ActiveWindow.ScrollRow = 377 ActiveWindow.ScrollRow = 368 ActiveWindow.ScrollRow = 359 ActiveWindow.ScrollRow = 350 ActiveWindow.ScrollRow = 340 ActiveWindow.ScrollRow = 332 ActiveWindow.ScrollRow = 323 ActiveWindow.ScrollRow = 314 ActiveWindow.ScrollRow = 305 ActiveWindow.ScrollRow = 291 ActiveWindow.ScrollRow = 284 ActiveWindow.ScrollRow = 277 ActiveWindow.ScrollRow = 271 ActiveWindow.ScrollRow = 264 ActiveWindow.ScrollRow = 259 ActiveWindow.ScrollRow = 254 ActiveWindow.ScrollRow = 248 ActiveWindow.ScrollRow = 245 ActiveWindow.ScrollRow = 240 ActiveWindow.ScrollRow = 235 ActiveWindow.ScrollRow = 229 ActiveWindow.ScrollRow = 227 ActiveWindow.ScrollRow = 223 ActiveWindow.ScrollRow = 220 ActiveWindow.ScrollRow = 217 ActiveWindow.ScrollRow = 215 ActiveWindow.ScrollRow = 212 ActiveWindow.ScrollRow = 209 ActiveWindow.ScrollRow = 207 ActiveWindow.ScrollRow = 204 ActiveWindow.ScrollRow = 201 ActiveWindow.ScrollRow = 197 ActiveWindow.ScrollRow = 194 ActiveWindow.ScrollRow = 190 ActiveWindow.ScrollRow = 188 ActiveWindow.ScrollRow = 184 ActiveWindow.ScrollRow = 180 ActiveWindow.ScrollRow = 176 ActiveWindow.ScrollRow = 171 ActiveWindow.ScrollRow = 167 ActiveWindow.ScrollRow = 161 ActiveWindow.ScrollRow = 157 ActiveWindow.ScrollRow = 152 ActiveWindow.ScrollRow = 148 ActiveWindow.ScrollRow = 142 ActiveWindow.ScrollRow = 136 ActiveWindow.ScrollRow = 131 ActiveWindow.ScrollRow = 125 ActiveWindow.ScrollRow = 120 ActiveWindow.ScrollRow = 117 ActiveWindow.ScrollRow = 113 ActiveWindow.ScrollRow = 110 ActiveWindow.ScrollRow = 106 ActiveWindow.ScrollRow = 104 ActiveWindow.ScrollRow = 100 ActiveWindow.ScrollRow = 97 ActiveWindow.ScrollRow = 93 ActiveWindow.ScrollRow = 91 ActiveWindow.ScrollRow = 87 ActiveWindow.ScrollRow = 84 ActiveWindow.ScrollRow = 79 ActiveWindow.ScrollRow = 76 ActiveWindow.ScrollRow = 72 ActiveWindow.ScrollRow = 70 ActiveWindow.ScrollRow = 66 ActiveWindow.ScrollRow = 64 ActiveWindow.ScrollRow = 60 ActiveWindow.ScrollRow = 58 ActiveWindow.ScrollRow = 54 ActiveWindow.ScrollRow = 51 ActiveWindow.ScrollRow = 47 ActiveWindow.ScrollRow = 45 ActiveWindow.ScrollRow = 42 ActiveWindow.ScrollRow = 40 ActiveWindow.ScrollRow = 36 ActiveWindow.ScrollRow = 34 ActiveWindow.ScrollRow = 32 ActiveWindow.ScrollRow = 31 ActiveWindow.ScrollRow = 29 ActiveWindow.ScrollRow = 27 ActiveWindow.ScrollRow = 23 ActiveWindow.ScrollRow = 14 ActiveWindow.ScrollRow = 13 ActiveWindow.ScrollRow = 10 ActiveWindow.ScrollRow = 9 ActiveWindow.ScrollRow = 8 ActiveWindow.ScrollRow = 7 ActiveWindow.ScrollRow = 5 ActiveWindow.ScrollRow = 3 ActiveWindow.ScrollRow = 2 ActiveWindow.ScrollRow = 1 Range("C5").Select ActiveWindow.ScrollWorkbookTabs Position:=xlFirst Sheets("Jan Statistics").Select Range("D2").Select Application.ScreenUpdating = True End Sub

• Answer:

  There is a lot of SCROLLING in this macro, none of which is necessary. Additionally, it is selecting cells to clear them ¨C again not necessary ¨C example range("G49:L49").clearcontents will do the same thing as Range("G49:L49").Select Selection.ClearContents But without moving the cursor. Again, there is a large amount of selecting cells followed by selecting other ones ¨C this is also unnecessary. Finally, a lot of copying and pasting is taking place ¨C again, I would not do this Range("h7:j7").Value = Range("h5:j5").Value As an example would set the value of the cells in H7 to J7 to whatever H5 to J5 contain ¨C this can be expanded to reference other sheets. All of this will streamline the macro and obviously if the cursor is never moving there SHOULD be no evidence of screen flash as there is nothing happening. That said, it¡¯s still odd that it IS flashing as you have screen updating turned off!