Excel Encounters a Problem when Using Macro Button

[Kappy]

I have a macro that copys values and deletes some sheets, then saves a copy.. I'll post the code below, but the code itself is not the problem.. it works exactly as it should.

The problem is that after it runs, excel shuts down and I get the message "excel has encountered a problem.." and something about needing to shut down and do I want to recover the file (this is in Excel 2010).

the STRANGEST part is that if I run the macro from the macro list, or from the visual basic editor... I DON'T encounter the problem..

however, if I use the command button that I set up, the macro still runs perfectly fine... but after running, excel shuts down!!

This is super frusterating, any help would be extremely appreciated!

Sub ValuedCopy()
Dim i As Integer, WkbName As String
Application.ScreenUpdating = False
For i = 2 To 5
Sheets(i).Select
Cells.Copy
Cells.PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
Range("F1").Select
Next i
Application.DisplayAlerts = False

Sheets(1).Delete

For i = 11 To 5 Step -1
Sheets(i).Delete
Next i
Application.DisplayAlerts = True
Sheets(1).Select
WkbName = ThisWorkbook.FullName
WkbName = Left(WkbName, Len(WkbName) - 5) & "_vc"
ThisWorkbook.SaveAs (WkbName)
Application.ScreenUpdating = True
End Sub

 

[AlphaFrog]

Is your button on one of the sheets you are deleting in the macro?

 

[Kappy]

Yes, it is. It's on sheet(1) at the beginning of the macro.

So I assume you're saying that this is the problem... but that still seems weird since that sheet is deleted before any of the others, and the macro continues to run properly after deleting that sheet...

Any chance you know of a solution that doesn't involve moving the button to one of the remaining sheets? (the whole point is for all the controls and sheets used for calcuation to be removed leaving the user with a valued copy of the report)

Thanks for helping

 

[AlphaFrog]

I get an error (Excel doesn't shut down though) if I have an ActiveX type command button on a sheet, and it runs code that deletes the sheet with the ActiveX command button. I'm guessing the problem with this method is that the code is contained in the sheet's module which gets deleted with the sheet before the code is completed.

If I use a Forms type command button assigned to a macro, and the macro deletes the sheet with the Forms type command button, I don't get an error. With this method, the code is stored in it's own module.

Note: I tested having an ActiveX command button call a macro (code is stored in a separate module and not in the sheet's module) and that didn't cause an error.

 

[Kappy]

Well that's interesting... and really just plain weird, because I am using a form control button assigned to a macro that is in its own module. And, or course as I mentioned, I'm not actually getting an error, just the shut down problem.

Anyway, I've basically given up (unless I find some new information).. I put the button on one of the pages that doesn't delete, and simply told the macro to delete the button before saving the workbook (working fine)... a little less professional looking, but oh well.

Thanks for your help AlphaFrog