Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.office.developer.vba
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming,microsoft.public.office.developer.vba
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming,microsoft.public.office.developer.vba
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
delete value on worksheet 2 that match values on worksheet 1 | Excel Discussion (Misc queries) | |||
pivot chart causes worksheet to deactivate | Excel Discussion (Misc queries) | |||
How to delete a duplicate Excel worksheet within that worksheet? | Excel Discussion (Misc queries) | |||
Deactivate worksheet | Excel Discussion (Misc queries) | |||
Deactivate web toolbar | Excel Discussion (Misc queries) |