Code to delete shapes each time a sheet is deactivated
 

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" wrote in message
...
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

Code to delete shapes each time a sheet is deactivated
 

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

Code to delete shapes each time a sheet is deactivated
 

I agree. Change ActiveSheet to Sh.
The deactivated sheet = Sh
The new sheet = ActiveSheet, so it gets erased.
Mike F
"Rick Rothstein (MVP - VB)" wrote in
message ...
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" wrote in message
...
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