Need help in VBA - Causing Excel to Crash

[TheGr8Wizard]

I am including the code below: The code should delete a sheet and then remove the index entry from the Main Page called "Working Lead"

When ever I delete a sheet Excel shuts down with an Unexpected error. Excel 2010 & Win7.
Look For The Green Comment Line below - This is where the crash happens -
Any Assistance would be appreciated.

THE CODE

Sub DelRecord()
Dim Answer1
Dim RowNum As Integer
Application.ScreenUpdating = False
'
' DelRecord Macro
' This Deletes the Active Record
'

'
    ActiveWorkbook.Unprotect Password:="Unlock"
    Sheets("Working Lead").Unprotect Password:="Unlock"
    If Range("E13").Value <> "Closed" Then
           MsgBox ("Before you can Delete a record NEXT FOLLOW UP Must be Set to CLOSED !!!")
           Range("E13").Select
           GoTo Out
    Else
        Answer1 = InputBox(Prompt:="ARE YOU SURE YOU WISH TO DELETE THIS RECORD -- THIS ACTION CANNOT BE UNDONE -- If you wish to DELETE the record Type YES in the Box Below", _
          Title:="Delete Record", Default:="No")
        Answer1 = WorksheetFunction.Proper(Answer1)
    End If
    
    If Answer1 = "Yes" Then
           RowNum = WorksheetFunction.Match(Range("H9").Value, Sheets("Working Lead").Range("H1:H10000"), 0)
           Application.DisplayAlerts = False
           ActiveSheet.Delete
           Application.DisplayAlerts = True
           Sheets("Working Lead").Select
'           THIS IS WHERE THE CRASH ERROR HAPPENS - START
           Range("A" & RowNum & ":H" & RowNum).Delete Shift:=xlUp
'           THIS IS WHERE THE CRASH ERROR HAPPENS - FINISH
           MsgBox ("Record Deleted")
           
    Else
           MsgBox ("Record Was NOT Deleted")
               
    End If

Out:
    Sheets("Working Lead").Protect Password:="Unlock", AllowFiltering:=True, AllowSorting:=True
    ActiveWorkbook.Protect Password:="Unlock", Structure:=True, Windows:=False
Application.ScreenUpdating = True
End Sub

 

[TheGr8Wizard]

Thank You very Much. This was my Problem. Still toying with the Idea of how to setup the control panel but it fixed my crash problem THANKS

 

[GTO]

Hi there,

Be patient, I am sure Rory will return as time/activities permit. I would hate to guess wrong, but maybe just a userform with a listbox to pick who's sheet to delete. If you really like the button on the sheet being deleted, and not to disagree, but I would think having the button's called procedure fetch another with OnTime would work.

Mark

 

[RoryA]

I'd use the Ribbon - assuming it's a 2007+ format file.

 

[TheGr8Wizard]

Thanks again for the Assistance. I setup a control panel using another sheet -- I thought this would allow for future expansion of functions. -- Again Thanks.

 

[GTO]

@RoryA:

Unfortunately, I still just use that for gift-wrapping...