2

What is behind the bug I have a workbook in which several sheets have lots of shapes (including arrows). When scrolling through the sheet, it happens that the shapes do not refresh. Arrows become invisible, shape outlines become dotted. The shapes are very much still there - sometimes the arrows are just partially invisible. After searching online I found that this is a known bug when a sheet has lots of shapes. it is not related to my monitor refreshing - I have dual monitors and taking excel from one to the other does not refresh. There are several ways to do it, though - 1) selecting cells behind the shape 2) minimizing and maximizing excel 3) Selecting the shape itself(sometimes works).

My workaround and question I made a macro to refresh the shapes. Basically it selects all cells, and returns to what the active cell was, in a blink. I have to launch the macro in the VBE or with a button right now, but it works perfectly :

Sub refreshShapes()
    Application.ScreenUpdating = False
    Dim active As Range
    Set active = ActiveCell

    Cells.Select
    active.Select
    Application.ScreenUpdating = True
End Sub

Now I would like this macro to launch every time the user scrolls the sheet and stops scrolling because the bug happens only then. Is there a way to do so without using a timer? Is there another known way to prevent this bug/work with it in a better way?

David G
  • 2,315
  • 1
  • 24
  • 39

1 Answers1

1

If you don't want to use a Timer then try including the Worksheet refresh macro in one of the available Worksheet events. See full list here. I would probably go with the SelectionChange event.

You might also want to analyse this article on how to capture the Scrolling event.

Btw. from this thread it seems a simple DoEvents will do instead of changing the ScreenUpdating property of the Application.

Community
  • 1
  • 1
AnalystCave.com
  • 4,884
  • 2
  • 22
  • 30
  • I'd rather turn `ScreenUpdating` to `False` to prevent the user from seeing the entire `Worksheet` being `Selected`. – David G Jun 02 '15 at 13:58
  • Whatever you prefer. Did this answer your question? – AnalystCave.com Jun 02 '15 at 13:59
  • Also, due to my macro contaiting a change of selection, putting it in the `SelectionChange` event caused Excel to crash when I tried it - it just looped itself over and over. – David G Jun 02 '15 at 14:03
  • 1
    I don't think `Cells.Select` and `active.Select` is necessary if you do `DoEvents` after the `ScreenUpdating = True`. Otherwise simply in your `SelectionChange` set a global variable before calling `Cells.Select` and in `SelectionChange` check whether the variable is set. If so then prevent code execution. At the end of the original `SelectionChange` unset the global variable – AnalystCave.com Jun 02 '15 at 14:07
  • Yes, that last comment could do the trick. Thanks! I will verify it later but I don,t see why it wouldn't work. – David G Jun 02 '15 at 15:49