Delete a worksheet before closing workbook

[Pawan]

Hello,

I want to delete all worksheets except "Data" before closing the workbook.
However, before closing that workbook, excel should warn the user and ask if
he really wants to close the book.

I tried to do this using BeforeClose event, but didnt succeed.
Can anyone help on this?

Thank You

Pawan

 

[Joel]

the code need to be in THISWORKBOOK VBA code sheet

Private Sub App_WorkbookBeforeClose(ByVal Wb as Workbook, _
Cancel as Boolean)
a = MsgBox("Do you really want to close the workbook?", _
vbYesNo)
If a = vbNo Then Cancel = True
for each sht in Sheets
if sht.name <> "Data" then
sht.delete
end if
next sht
workbook.save
End Sub

 

[Mike H]

Maybe

Private Sub Workbook_BeforeClose(Cancel As Boolean)
msg = "Warning continuing will delete all worksheets except Data" & Chr(10)
& _
"Do you want to continue? Press Cancel to stop deletion OK to continue"
response = MsgBox(msg, vbOKCancel, "WARNING")
If response = vbCancel Then Exit Sub
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Worksheets
ws.Select
If ws.Name <> "Data" Then
ws.Delete
End If
Next
Application.DisplayAlerts = True
ActiveWorkbook.Save
End Sub

Mike

 

[Pawan]

Its working but there is little problem.

If I change something in the sheet and close the workbook, then excel (and
not our code in VBA) asks me if I want to save changes. After I click any
option, the VBA code doesn't execute and workbook closes.

Also I missed one thing. If after displaying msg through VBA, ig user
selects 'No', then workbook must be kept open (means close command should not
be accepted by excel).

Is it possible?

Thank You

Regards,
Pawan

 

[Mike H]

Try this small change

Private Sub Workbook_BeforeClose(Cancel As Boolean)
msg = "Warning continuing will delete all worksheets except Data" & Chr(10)
& _
"Do you want to continue? Press Cancel to stop deletion OK to continue"
response = MsgBox(msg, vbOKCancel, "WARNING")
If response = vbCancel Then
Cancel = True
Exit Sub
End If
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Worksheets
ws.Select
If ws.Name <> "Data" Then
ws.Delete
End If
Next
Application.DisplayAlerts = True
ActiveWorkbook.Save
End Sub

Mike