I have a workbook with 3 work sheets, first tab is for data input, second tab
is for data maintain, third tab is macro auto add sheet which extract data
from data maintain sheet for reporting purpose. I would like to delete the
third sheet when either users switch to other tab or closing workbook. I get
the €œdelete€� code but dont know the code when users switch tab or close book.

Application.DisplayAlerts = False
Sheets("Print").Delete
Application.DisplayAlerts = True

Try the Workbook close event..From VBE double click 'ThisWorkbook'

Private Sub Workbook_BeforeClose(Cancel As Boolean)

OR

get the Sheet DeActivate event of the 3rd sheet
Private Sub Worksheet_Deactivate()


If this post helps click Yes
---------------
Jacob Skaria


"Seeker" wrote:

I have a workbook with 3 work sheets, first tab is for data input, second tab
is for data maintain, third tab is macro auto add sheet which extract data
from data maintain sheet for reporting purpose. I would like to delete the
third sheet when either users switch to other tab or closing workbook. I get
the €œdelete€� code but dont know the code when users switch tab or close book.

Application.DisplayAlerts = False
Sheets("Print").Delete
Application.DisplayAlerts = True

Hi Jacob,
I added the code in my first post to Private Sub Workbook_BeforeClose(Cancel
As Boolean), the sheet was deleted before book closed, thats great.
I have problem in the Sheet DeActivate event. The event code is attached to
the sheet when I built it - say Sheet1(Print), when changing tab, it only
works once, thereafter, code crash.
1) As Print sheet is added by macro upon request only, the sheet number
changes from Sheet1(Print) to Sheet2(Print) and so on, so the code cannot be
kept and attached to the new added (Print).
2) If I close the work book while I am on the Print page, both
€œWorkbook_BeforeClose€� and €œSheet DeActivate event€� activate at the same time
caused one of the €œdelete€� action cannot be performed.
Any solution to solve this please?
Rgds

"Jacob Skaria" wrote:

Try the Workbook close event..From VBE double click 'ThisWorkbook'

Private Sub Workbook_BeforeClose(Cancel As Boolean)

OR

get the Sheet DeActivate event of the 3rd sheet
Private Sub Worksheet_Deactivate()


If this post helps click Yes
---------------
Jacob Skaria


"Seeker" wrote:

I have a workbook with 3 work sheets, first tab is for data input, second tab
is for data maintain, third tab is macro auto add sheet which extract data
from data maintain sheet for reporting purpose. I would like to delete the
third sheet when either users switch to other tab or closing workbook. I get
the €œdelete€� code but dont know the code when users switch tab or close book.

Application.DisplayAlerts = False
Sheets("Print").Delete
Application.DisplayAlerts = True

1. Use the below procedure to delete the sheet which contains the work
"Print". Call this procedure from Workbook close. If sheet exists the sheet
will be deleted..

Call DeletePrintSheet.

Sub DeletePrintSheet()
For intTEmp = 1 To Sheets.Count
If InStr(1, Sheets(intTEmp).Name, "print", vbTextCompare) < 0 Then
Application.DisplayAlerts = False
Sheets(intTEmp).Delete: Exit For
Application.DisplayAlerts = True
End If
Next
End Sub

2. When you delete the sheet any code attached to that sheet will also get
deleted. So placing the code at deactivate will work only once...Why dont you
just hide the worksheet upon deactivate and then without adding a worksheet
just set the visible property to True. If you have any formatting or data
which needs to be cleared; clear those using code and keep it hidden...

Sheets("Sheet4").visible = false

If this post helps click Yes
---------------
Jacob Skaria


"Seeker" wrote:

Hi Jacob,
I added the code in my first post to Private Sub Workbook_BeforeClose(Cancel
As Boolean), the sheet was deleted before book closed, thats great.
I have problem in the Sheet DeActivate event. The event code is attached to
the sheet when I built it - say Sheet1(Print), when changing tab, it only
works once, thereafter, code crash.
1) As Print sheet is added by macro upon request only, the sheet number
changes from Sheet1(Print) to Sheet2(Print) and so on, so the code cannot be
kept and attached to the new added (Print).
2) If I close the work book while I am on the Print page, both
€œWorkbook_BeforeClose€� and €œSheet DeActivate event€� activate at the same time
caused one of the €œdelete€� action cannot be performed.
Any solution to solve this please?
Rgds

"Jacob Skaria" wrote:

Try the Workbook close event..From VBE double click 'ThisWorkbook'

Private Sub Workbook_BeforeClose(Cancel As Boolean)

OR

get the Sheet DeActivate event of the 3rd sheet
Private Sub Worksheet_Deactivate()


If this post helps click Yes
---------------
Jacob Skaria


"Seeker" wrote:

I have a workbook with 3 work sheets, first tab is for data input, second tab
is for data maintain, third tab is macro auto add sheet which extract data
from data maintain sheet for reporting purpose. I would like to delete the
third sheet when either users switch to other tab or closing workbook. I get
the €œdelete€� code but dont know the code when users switch tab or close book.

Application.DisplayAlerts = False
Sheets("Print").Delete
Application.DisplayAlerts = True

Tks Jacob.

"Jacob Skaria" wrote:

1. Use the below procedure to delete the sheet which contains the work
"Print". Call this procedure from Workbook close. If sheet exists the sheet
will be deleted..

Call DeletePrintSheet.

Sub DeletePrintSheet()
For intTEmp = 1 To Sheets.Count
If InStr(1, Sheets(intTEmp).Name, "print", vbTextCompare) < 0 Then
Application.DisplayAlerts = False
Sheets(intTEmp).Delete: Exit For
Application.DisplayAlerts = True
End If
Next
End Sub

2. When you delete the sheet any code attached to that sheet will also get
deleted. So placing the code at deactivate will work only once...Why dont you
just hide the worksheet upon deactivate and then without adding a worksheet
just set the visible property to True. If you have any formatting or data
which needs to be cleared; clear those using code and keep it hidden...

Sheets("Sheet4").visible = false

If this post helps click Yes
---------------
Jacob Skaria


"Seeker" wrote:

Hi Jacob,
I added the code in my first post to Private Sub Workbook_BeforeClose(Cancel
As Boolean), the sheet was deleted before book closed, thats great.
I have problem in the Sheet DeActivate event. The event code is attached to
the sheet when I built it - say Sheet1(Print), when changing tab, it only
works once, thereafter, code crash.
1) As Print sheet is added by macro upon request only, the sheet number
changes from Sheet1(Print) to Sheet2(Print) and so on, so the code cannot be
kept and attached to the new added (Print).
2) If I close the work book while I am on the Print page, both
€œWorkbook_BeforeClose€� and €œSheet DeActivate event€� activate at the same time
caused one of the €œdelete€� action cannot be performed.
Any solution to solve this please?
Rgds

"Jacob Skaria" wrote:

Try the Workbook close event..From VBE double click 'ThisWorkbook'

Private Sub Workbook_BeforeClose(Cancel As Boolean)

OR

get the Sheet DeActivate event of the 3rd sheet
Private Sub Worksheet_Deactivate()


If this post helps click Yes
---------------
Jacob Skaria


"Seeker" wrote:

I have a workbook with 3 work sheets, first tab is for data input, second tab
is for data maintain, third tab is macro auto add sheet which extract data
from data maintain sheet for reporting purpose. I would like to delete the
third sheet when either users switch to other tab or closing workbook. I get
the €œdelete€� code but dont know the code when users switch tab or close book.

Application.DisplayAlerts = False
Sheets("Print").Delete
Application.DisplayAlerts = True