Delete Sheet Macro Crashes Excel

[zoom38]

Good morning, I have a worksheet that has a couple of buttons on it, has a window_activate event, worksheet_change event and enableevents is set to false in both events. I have a simple macro (below) that is used to delete the worksheet. However when invoked, it crashes Excel on the "ActiveSheet.Delete" line. After some trial and error, the macro will delete the sheet without crashing excel after the buttons are removed from the worksheet and all code is removed from the worksheet including the commandbutton_click events, worksheet_activate event and worksheet_change event. However I can manually delete the sheet by right clicking on the sheet tab and selecting delete with no issues. Seems buggy to me.
Has anyone come across this issue and found a good resolution or workaround? Maybe code that deletes the sheet code and buttons before deleting the sheet? Or possibly something else?

Sub deletesheet()
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False
   
        ActiveSheet.Delete
    
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Thanks in advance.
Gary

[zoom38]

Please disregard this thread. It appears that I have other issues that are causing the problem that I have to look into.

Thanks
Gary

[zoom38]

Ok, so I think I found the cause, I have the following formulas in some cells on multiple sheets.

=IF(OR(Incident_Start_Date="",Incident_Start_Date="MM/DD/YY"),"MM/DD/YY",ChangeDateFormat(Incident_Start_Date))

=IF(Incident_Start_Time="HHMM","HHMM",Incident_Start_Time)

=IF(OR((ISERR(ChangeDateFormat(Operational_Period_Begin_Date))),ISERR(ChangeDateFormat(Operational_Period_End_Date))), "MM/DD/YY HHMM - MM/DD/YY HHMM", ChangeDateFormat(Operational_Period_Begin_Date)& " " & Operational_Period_Begin_Time & " - " & ChangeDateFormat( Operational_Period_End_Date) & " " & Operational_Period_End_Time)

Can anyone tell me why these formulas prevents the deletesheet macro form operating correctly?
Is there a way to disable the sheet formulas first then invoke the deletesheet macro?


Gary

[zoom38]

In scouring the internet for a cause and solution I determined that there really is no single cause for this behavior and I couldn't find a solution. It also appears to be a comman issue. However I eventually determined that the cell formula below with functions built in caused Excel to crash when deleting a sheet by macro. I'm not sure why it occurred, must be my "ChangeDateFormat" function but if you run into the same situation, it might be worthwhile to look into your worksheet formulas that contain functions you've created.

I tried disabling calculation but that didn't work so I created code in vba that replaced the worksheet formula which solved the problem.

 
 
=IF(OR((ISERR(ChangeDateFormat(Operational_Period_Begin_Date))),ISERR(ChangeDateFormat(Operational_Period_End_Date))), "MM/DD/YY HHMM - MM/DD/YY HHMM", ChangeDateFormat(Operational_Period_Begin_Date)& " " & Operational_Period_Begin_Time & " - " & ChangeDateFormat( Operational_Period_End_Date) & " " & Operational_Period_End_Time)

I hope this helps someone else should they run into the same problem.

Gary