Delete Worksheet on DeActivate
 

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

Delete Worksheet on DeActivate
 

Have you tried this in the ThisWorkbook module.

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


--
Don Guillett
SalesAid Software

"TJ Walls" wrote in message
pam.com...
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

Delete Worksheet on DeActivate
 

the workbook level sheetactivate event sounds like it should work.

--
Regards,
Tom Ogilvy

"TJ Walls" wrote in message
pam.com...
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

Delete Worksheet on DeActivate
 

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

On Mon, 17 May 2004 12:30:25 -0500, Don Guillett wrote:

Have you tried this in the ThisWorkbook module.

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