CODEWITHSUNDEEP
c#excelvstoexcel-2007vba
Shakti saxena
Shakti saxena

Reputation: 183

Deletion of sheet on prompt in Excel

I am writing a excel addin. I want to have a functionality in my code such that when I right click on sheet tab in my Activeworkbook and click on delete.. I can pop up a custom message and capture the return value of the dialog box and based on YES or NO, I can decide if I should proceed with deleting the sheet or not? Kindly Help.

Upvotes: 2

Views: 561

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149325

Unfortunately there is no specific event which handles the Sheet Deletion event. Having said that there can be three options that I can think of but all three have their own cons

OPTION 1

This method involves protecting the workbook structure. This will disallow the user to delete any sheet. If they want to delete a sheet, it has to be via an option from your add-in. Perhaps a button?

Cons: Not practical to protect the structure of every workbook. But if you want the functionality for a specific workbook then this is the best way to go about it.

OPTION 2

This method involves, creating a temp sheet and hiding it in the active workbook. The temp sheet is created when the workbook opens and is deleted when the workbook closes. Every time a sheet is activated, the data from the active sheet is copied to the temp sheet so that if the user deletes an unwanted sheet, you could always restore it. The reason why we are taking this approach is because the UnDo doesn't work to recover deleted sheets.

Also when a sheet is added or deleted, some sheet gets activated. Hence we are going to use the Workbook_SheetActivate event so that we can then check for the worksheet count and reach to a conclusion if the sheet was deleted or added.

Here is a sample code that I wrote to demonstrate on how it works. You can simply enhance it to suit your needs.

Const wsTempName As String = "MyAddinTempSheet"

Dim wsTemp As Worksheet
Dim TempSheetCount As Long
Dim boolFirst As Boolean

Private Sub Workbook_Open()
    boolFirst = True
    Set wsTemp = ThisWorkbook.Sheets.Add
    wsTemp.Name = wsTempName
    wsTemp.Visible = xlSheetHidden
    ThisWorkbook.ActiveSheet.Cells.Copy wsTemp.Cells
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Sheets(wsTempName).Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim wsCount As Long

    If boolFirst = True Then
        TempSheetCount = ThisWorkbook.Sheets.Count
        boolFirst = False
    Else
        wsCount = ThisWorkbook.Sheets.Count

        If wsCount < TempSheetCount Then
            MsgBox "Sheet(s) was/were deleted"
        ElseIf wsCount > TempSheetCount Then
             MsgBox "Sheet(s) was/were Added"
        End If
    End If
End Sub

Cons: Creating a backup of a worksheet can be a nasty affair if the sheet has lots of data.

OPTION 3

HERE is another method suggested by Andy Pope.

Cons As stated in that link, it gets more complicated with multi workbooks.

Upvotes: 1

Related Questions