Code to delete shapes each time a sheet is deactivated

[Rick Rothstein \(MVP - VB\)]

Just a guess.... shouldn't you be using the Sh object argument from the
SheetDeactivate event call rather than the ActiveSheet object in your code?

Rick

 

[DlgomesBR]

Please, someone help me in this problem:

I have a workbook with several sheets. In each sheet a have a lot of
floating shapes created dinammicaly each time the sheet is activated. I need
a code that each time I leave the active sheet, the shapes could be deleted
before the new sheet is activated. If I don't do this way, my workbook is
getting very large (currently 20MB). I am using a code below, but it is
deleting shapes in the new sheet and not in the sheet I was on.

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Dim myshape As Shape, myrange As Range
Set myrange = ActiveSheet.Range(Cells(Range("intervalo1up").Row + 1, 5),
Cells(Range("UltimaCelula").Row, 5))
For Each myshape In ActiveSheet.Shapes
If Intersect(myshape.TopLeftCell, myrange) Is Nothing Then
'do nothing
Else
myshape.Delete
End If
Next myshape
End Sub

 

[Mike Fogleman]

I agree. Change ActiveSheet to Sh.
The deactivated sheet = Sh
The new sheet = ActiveSheet, so it gets erased.
Mike F