CODEWITHSUNDEEP
excelvba
Error 1004
Error 1004

Reputation: 8230

Delete/Add sheets event - VBA

Is there any event which triggered on sheet deletion AND addition? i have try 

Private Sub Workbook_NewSheet(ByVal Sh As Object)
End Sub

but this event works only when i add a sheet. In my case i need something to works in both Add & Delete.

Any help will appreciate.

Upvotes: 2

Views: 1669

Answers (3)

Siddharth Rout
Siddharth Rout

Reputation: 149325

Here is another way

Logic:

  1. Before the sheet is deleted, count the number of sheets in the workbook
  2. When a sheet is activated, check the number of sheets in the workbook

Code:

Dim PreviousWorksheetCount As Integer

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim CurrentWorksheetCount As Integer

    CurrentWorksheetCount = ThisWorkbook.Sheets.Count

    If CurrentWorksheetCount < PreviousWorksheetCount Then
        MsgBox "Sheet was deleted"
    End If
End Sub

Private Sub Workbook_SheetBeforeDelete(ByVal Sh As Object)
    PreviousWorksheetCount = ThisWorkbook.Sheets.Count
End Sub

Upvotes: 4

sancho.s ReinstateMonicaCellio
sancho.s ReinstateMonicaCellio

Reputation: 15631

There is no single event that captures either addition (Workbook.NewSheet) or deletion (Workbook.SheetBeforeDelete). But you could detect both events separately, and run the same piece of code for both events.

To be able to run code after sheet deletion, you may use a timer to allow for deletion to take place. Even better, you may set up a waiting loop that counts the number of sheets in the workbook, and which is only exited when the number of sheets is the expected number after deletion is completed. Then you execute your intended code.

Note that there is an issue when the deleted sheet is a Chart. For those cases, you can use the suggestion here.

Upvotes: 1

Zsmaster
Zsmaster

Reputation: 1559

Here you are a delete sheet event.

Put this code in a standard code module

Public shName As String

Sub Deletesheet()
    Dim oWS As Object
    On Error Resume Next
    Set oWS = Sheets(shName)
    If oWS Is Nothing Then
        MsgBox shName & " has been deleted"
    End If
End Sub

Put this in ThisWorkbook

Private Sub Workbook_SheetDeactivate(ByVal sh As Object)
    shName = sh.Name
    Application.OnTime Now + TimeSerial(0, 0, 1), "DeleteSheet"
End Sub

Upvotes: 2

Related Questions