Delete Worksheet on DeActivate

[TJ Walls]

Hello All,

I have a worksheet that is created at run-time that displays just some
temporary graphs to the user. I would like to delete this worksheet once
the user changes focus back to another sheet in the workbook.

My original idea was to hook in an event handler for the
Worksheet_DeActivate event. The code looked like (don't have the exact
code with me, but the idea):

[Public Class Module clsWks]
Public WithEvents oWks As Excel.Worksheet

Private Sub oWks_DeActivate()
DeleteWorksheet
'OR
'Me.oWks.Delete
End Sub


[Module TestClass]
Dim gWksCol as New Collection

Private Sub CreateSheet()
Dim oWksEvts As clsWks

Set oWksEvts = New clsWks
Set oWksEvts.oWks = Worksheets.Add

...

gWksCol.Add oWksEvts
End Sub

Sub DeleteWorksheet()
gWksCol(1).oWks.Delete
gWksCol.Remove 1
End Sub

The code craps out with a run-time error both when calling
DeleteWorksheet or Me.oWks.Delete in the DeActivate event. I think this is
beacuse the delete method seems to re-fire the DeActivate event on the
worksheet which has already been deleted.

Is there a better way to delete a worksheet when it is no longer the
ActivateSheet? Is it better to hook into the WorkBook_SheetChange event,
or is this the correct path and I've just done something silly?

Thanks,
TJ Walls
Ph.D. Candidate - Dept of Physics, Stony Brook University

 

[Don Guillett]

Have you tried this in the ThisWorkbook module.

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If Worksheet.Name = "Joe" Then etc
End Sub

 

[Tom Ogilvy]

the workbook level sheetactivate event sounds like it should work.

 

[TJ Walls]

Hi Don,

The problem is that ThisWork is acting as simply an container for my VBA
code. All the "action" happens in workbooks created/opened dynamically by
my code. I'll think hooking into the Workbook events may eliminate the
conceptual problem of the Worksheet trying to delete itself ... I was
hoping someone that has done this before may have fallen into this trap and
have some tips.

Thanks,
TJ
Ph.D. Candidate - Dept. of Physics, Stony Brook University