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

 

[mole999]

If I read this correctly

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

you select a sheet
then delete it
then try to select it again
which begs the question of why you are then trying to delete a row on a non existent sheet

 

[Michael M]

What is the name of the sheet to be deleted?
If you are running this from the "Working Lead" Sheet, it will be the one deleted !!

 

[TheGr8Wizard]

Sorry Guys --- I guess I wasn't Clear. The Working Lead Sheet is an Index of Clients, The rest of the sheets we have one for each client. When I client is no longer active, We delete the individual client sheet and remove their entry from the Working leads page.

-@Mole - the activesheet Delete Command you are seeing is on the clients individual sheet. Then I transfer to the WorkingLead Sheet

-@Michael - The Name of the sheet is the Last Name of the Client - I could assign it to a variable if that would be helpful?

@Both - It seems to crash when I delete the row in the Working Leads sheets - When I have commented it out and ran it the crash does not seem to occur.
THANKS FOR YOUR HELP

 

[RoryA]

How are you triggering the code?

 

[TheGr8Wizard]

I am Triggering the code using a button on the sheet that is being deleted. So on the client sheet there is a button that says "Delete this record" and after a few safety checks in the Macro I delete the sheet.

I hope this helps

Tim

 

[RoryA]

Where's the button? Anywhere near the row you're deleting?

 

[TheGr8Wizard]

No it is on the Sheet being deleted -- Is this a Problem???

 

[RoryA]

I wouldn't recommend deleting the button that's running the code while the code is running. Why not use a menu?

 

[TheGr8Wizard]

Ok - I think I see how this could cause the Problem. I am interested in your menu Idea. What would that look like? Were you thinking using Validation List or some other Method?