Forum Discussion

lobo114's avatar
lobo114
Brass Contributor
Oct 24, 2023

Edit a recorded macro to include an IF statement

In the attached document, I have an example of a macro I recorded. I would like to write an IF statement at the beginning to say, if the "Home" sheet is deleted, then run the rest of the macro. Any h...
Macros and VBA
lobo114's avatar
lobo114
Brass Contributor
Oct 25, 2023
It does do what I want when I run the macro. What I am looking for it to do is, if the "Home" worksheet tab in my Excel document is manually deleted by a user, the macro will automatically run. Currently if I delete the "Home" tab, nothing happens until I run the macro. I was wondering if I need an IF statement in the macro? Basically, IF the "Home" tab is deleted, run the macro.
HansVogelaar's avatar
HansVogelaar
MVP
Oct 25, 2023

lobo114 

Change the macro to

Sub Macro2()
    With workSheets("Work Center Template")
        .Unprotect
        .Shapes("Group 6").Delete
        .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    End With
    With Sheets("SAC Template")
        .Unprotect
        .Shapes.Range("Group 9").Delete
        .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, _
            AllowInsertingRows:=True, AllowInsertingHyperlinks:=True, _
            AllowDeletingRows:=True
    End With
End Sub

Double-click ThisWorkbook  in the Project Explorer pane on the left.

Copy the following code into the ThisWorkbook module:

Private Sub Workbook_SheetBeforeDelete(ByVal Sh As Object)
    If Sh.Name = "Home" Then
        Call Macro2
    End If
End Sub

Resources