Excel Encounters a Problem when Using Macro Button

Kappy

Board Regular
Joined
Jun 26, 2009
Messages
58
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
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Kappy

Board Regular
Joined
Jun 26, 2009
Messages
58
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,265
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.
 
Last edited:

Kappy

Board Regular
Joined
Jun 26, 2009
Messages
58
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
 

Watch MrExcel Video

Forum statistics

Threads
1,101,800
Messages
5,482,973
Members
407,371
Latest member
gdjenkins80

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top