Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Edit a recorded macro to include an IF statement

Copper Contributor

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 help would be appreciated! Thanks

6 Replies

@lobo114 

I don't understand. Your macro starts by deleting the Home sheet, so the rest should always be executed...

I'm not sure why it doesn't work either. What I want to happen is if the user deletes the home sheet, the rest of the macro runs, but nothing happens when I delete the home tab. If I run the macro then the macro deletes the home tab and everything else below it works. I want the macro to work when the home tab is manually deleted.

@lobo114 

Does this do what you want?

Sub Macro2()
    Application.DisplayAlerts = False
    On Error Resume Next
    workSheets("Home").Delete
    On Error GoTo 0
    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
I think so, I will try it out in the morning and let you know
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.

@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