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
HansVogelaar's avatar
HansVogelaar
MVP
Oct 24, 2023

lobo114 

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

  • lobo114's avatar
    lobo114
    Brass Contributor
    Oct 24, 2023
    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.
    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Oct 24, 2023

      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
      • 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.

Resources