I have the following strange problem. A simple macro 'ActiveWorkbook.Close' causes excel to crash if I use it with a button on my sheet (a form). When I run the macro via developer > macros > run
the macro runs as expected. Even if a run the macro with alerts (thanks to jproffer) the problem persist.
I'm working on developing a stock option trading system in Excel using a live data feed and VBA. It tends to crash after running flawlessly for a half hour to 2 hours. It seems to crash when loading a support workbook, so I was hoping you could take a peek at my code and see if there's a problem with the way I'm dynamically opening/closing workbooks.
In order to cope with limitations in the amount of live data that I'm accessing (through DDE with Reuters Station), I've split up the data I need into about 600 workbooks, which open when they're needed or close when they're not (through VBA). At any given time, about 35-40 of these workbooks are open.
The main workbook that uses all this live data and support workbooks is set to have the application recalculate every 5 seconds (automatic recalc crashes the program almost immediately). After that refresh, it calls other subroutines, including opening/closing these support workbooks:
I have a Macro that is stored in a specific workbook,"Projection Modeler.xls" the last line performed in this macro is the close workbooks without saving chages(this needs to be done due to the way this macro was written, and it would take too long to rewrite). If I would then like to reopen this workbook, and then rerun this macro, can I do this from inside the current macro (in a new sub routine, for instance), or do I have to write a macro outside of the workbook and then open the workbook and call the routine?
View 4 Replies View RelatedI have a workbook that contains 4 macros. This workbook is copied by vb.net application with another name. When I run the macro in the copied worksheet, the Original file/workbook is opened automatically, and it is open until the copied workbook is closed.
My question:
1) Is this normal, when a copied workbook invokes a macro from the original workbook, the original workbook opens?
2) Is it possible to close the original workbook while executing the macro from the copied workbook?
3) Will that affect the macro in the copied workbook?
4) If possible to close the original workbook, kindly help me with the code.
When I open my workbook it also opens a separate workbook and hides second book.
when I close myworkbook I want to unhide the hidden one and close it without saving.
i m designing a test for a school and want to add a macro that will be assigned to a button at the end of the test i want the macro to close down the the excel program running and not prompt the user to save the workbook is there a vb code that can do this i have tryed a few but they all come back object not found.
View 6 Replies View RelatedI need to have a workbook (all of them in a folder, ideally) refresh in the middle of the night. The file is large so the refresh takes a full minute. I know I can't auto refresh a closed workbook (pretty sure anyway), and I can't leave the workbook open for various reasons.
So I'm thinking I could have a macro in an open excel (it kicks off on open) that will open a particular workbook elsewhere (the one I need refreshed), open it, refresh it and close/save it. Ideally it would do this for the entire group of workbooks in the folder but I'd be happy to start with just one. I would like to set it to occur every twelve hours (so at midnight, say, this would occur - I would just have to leave the workbook with the macro in it open on a computer so that it runs and completes this task every night).
I have a macro on a particular workbook that runs when the workbook closes.
This runs fine when I close the workbook itself. If I happen to have other workbooks open and then I close EXCEL rather than an individual workbook, my macro that runs on close always runs when it is not the active workbook.
To put it another way, say that my 'run on close' macro is workbook 1. I also have workbooks 2 and 3 open as well. If workbook 3 is the active workbook and I choose to close Excel (and thus close all three workbooks), my 'run on close' macro runs straight away and, worse still, will run it on workbook 3 (my active workbook).
How can I stop the macro running until it is the active workbook in the closing chain, so that it only runs on the correct workbook? (workbook 1 in my example)
I am relatively new to writing script in excel. I have a 34 sheet workbook that includes inputs, intermediate calculations and then reporting outputs.
I am trying to write macros to do the following
1. On opening - all sheets except a contents page (with macro buttons on it) are hidden
2. One of the macros on this page will unhide all sheets (i.e. a 'developer mode')
3. One of the macros will unhide all relevant input and reporting sheets (i.e. a 'user mode')
I originally set up macros to run on workbook opening that hid all sheets except the contents page and this was working but ran into trouble when I opened up the 'User Mode' saved my changes and exited - the macro didnt work when I went to open again (I think because some of the sheets were visible and some werent and it was trying to hide sheets that were already hidden).
To counter this I set up a macro that made all worksheets visible on workbook closing - this works except I keep getting a 'Save' prompt from excel because the location of selected cells (or the sheet I am viewing) has changed from last opening (not to mention the inputs may change).
I am thinking the best way to fix this as follows:
1. Keep the opening macro the same
2. Keep the user/developer macros the same
3. On closing - develop a macro that unhides all sheets after the user has selected whether or not they have made changes to the sheet in a pop up message box - if they choose yes then they are prompted to save as and then the macro to hide all sheets runs on the new version - if they choose no then they are not prompted to save the document (i.e. next time they open they will be opening back at the contents page with the same inputs they started with in the previous session).
I have a macro that runs when the workbook opens. At the end of my macro I've added a Yes/No box. If 'No' the workbook closes without saving. If 'Yes' I would like to restart the macro. I can close it ok, but I don't know how to restart the macro from line 1 when 'Yes' is selected. Here is what I have:
YesNo = MsgBox("Done! Would you like to run again?", vbYesNo + vbCritical, "Caution")
Select Case YesNo
Case vbYes
'I don't know what to put here :(
Case vbNo
ActiveWorkbook.Close savechanges:=False
End Select
I am using Excel 2003.
It’s been working perfectly up until around an hour ago. However, now when I close Excel, I get compile errors.
These compile errors seem to be because Excel is trying to access controls in the workbook after it has closed. Since the workbook is closed, VBA can no longer ‘see’ the controls, and therefore it thows up errors.
I’m also getting a similar error on a Worksheets("DataExplorer").unprotect line, which seems to be because the worksheet isn’t there after closing.
These errors only occur when I close the whole excel application using the big cross in the top right. If I just close the workbook (using the smaller cross just below the big cross in the top right of excel), it doesn’t throw up these errors.
Just to clarify: all of the code runs perfectly when Excel is open. The errors are being thrown up for lines of code which run without problem until Excel is closed.
I have a macro that opens all workbooks from one directory and runs a macro for each workbook to clean up the data. I cannot figure out how to take all those open workbooks and save them to another directory and close the workbook. Also, I do not want the macro workbook (xlsm) to save. I only want it to close. I am working in 2007 Excel.
View 8 Replies View RelatedAs I've learned to develop Excel apps with vba over the years I've noticed that, if the Excel app starts to get too large (over 1MB) saving it will often cause the application to crash for no apparent reason - the result being that I lose all the vba code I entered before the last save - with the worst case being that, on occasion I have lost all of the vba code in an applications!!!
So, I've learned to back up often using a third-party utility that I use to save my applications, which works wonders in allowing me to save my application without it crashing. The application is called Code Cleaner 97.exe. It was designed around Excel 97 (which I still use) but if possible I'd like to get an updated version for Excel 2003 and Excel 2007, which I also use.
Does anyone know of a more recent version of this utility, or another similar utility that allows large Excel applications to be properly saved, etc?
Code Cleaner 97.exe was a freebie that was available through one of the old Compuserve Excel forums. It was a very popular utility and was suggested as a means to save one's Excel apps without the app getting trashed, etc.
I'm not expecting a freebie solution so I'll gladly pay for something that allows me to save my large Excel apps without them crashing, etc.
I’m launching a new workbook, exporting some stuff, basically creating a report. But then I want to add a BeforePrint event to the new worksheet. Both of the macro below works great right up until I change “Sheet1” to “ThisWorkBook”…then Excel crashes.
Sub AddCode1()
Dim VBCodeMod As CodeModule
Dim LineNum As Long
Dim Copybook As Workbook
Set Copybook = Excel.Workbooks.Add
Set VBCodeMod = Copybook.VBProject.VBComponents("sheet1").CodeModule
LineNum = VBCodeMod.CountOfLines + 1
VBCodeMod.InsertLines LineNum, "Private Sub Workbook_BeforePrint(Cancel As Boolean)" & Chr(13) & "if ActiveSheet.CodeName <> ""Sheet1"" or ActiveSheet.CodeName <> ""Sheet2"" or ActiveSheet.CodeName <> ""Sheet3"" then" & Chr(13) & "MsgBox(""This Electronic Outcome Review Summary Report is NOT optimized for Printing......................................
Three times (by three different users) over the past two months, a workbook has gotten into what appears to be some sort of corrupted state. When you attempt to open the workbook you get the "report to Microsoft" screen which states "Microsoft Excel has encountered a problem and needs to close". Proceeding on this screen closes the entire instance of Excel, including the offending workbook and any others that are open. However, if I simply open and save the workbook in Excel safe mode, then it resolves the problem. But, again, it has happened three times so it keeps coming back.
A little more background: there is a custom add-in referenced which contains many custom functions. This add-in is also referenced by other workbooks, none of which have had this problem. The Workbook_Open event only does what I consider to be superficial processing; it hides some sheets, selects a sheet, defaults some shape colors, and defaults a checkbox to True. In other words, nothing too heavy going on at startup, in my opinion. I have tried several times to break into the code between the time the workbook begins to load and when the "report to Microsoft" screen is displayed, but this was unsuccessful.
I was hoping that the fact that simply saving it from safe mode resolves the problem might indicate the problem to someone on the forum. I have a copy of the file in its corrupted state but I have not attached it because it contains some business sensitive information.
I use spinbuttons to increment 3 data cells (C7:C9). These cells are used as entry data for some calculations and also trigger some macros via change event. Every time I use them the workbook crashes, this is only since I modified one of the instantiated macros that sorts a table on another sheet. See below the spinbutton code with the call of the macro and further the code of the sorting macro:
Private Sub SpinButton1_SpinDown()
Range("C7").Value = Range("C7").Value - 0.05
End Sub
Private Sub SpinButton1_SpinUp()
Range("C7").Value = Range("C7").Value + 0.05
End Sub
Private Sub SpinButton2_SpinDown()
Range("C8").Value = Range("C8").Value - 0.05
End Sub......................................................
I'm writing an application in Excel, its a little over 5MB after I've run through UsedRegion and trimmed stuff, 94 worksheets. I'm encountering a fair number of crashes these days. My OS is fully patched as is MS Office. Running Windows server 2003 and Excel 2007.
I was wondering about the pros and cons of writing an application in one large workbook or many smaller ones. So far its been one large one but these crashes are really impeding my progress.
What's the best way to remedy these crashes or even start to diagnose?
The code below resides in my personal.XLS Module. When I step through the code everything works till it reaches the point in red then I get the pop up that says excel needs to close and asks to send or not to send.
I noticed that if I choose recover my work and restart excel and I look at the code in THISWORKBOOK of the recovered workbook that the code I wanted placed there is there.
Can anyone explain why this is happening? I have the macro's security set to low and a check mark in the trust access to vb projects.
Any Idea's how to fix this?
I have data that daily needs to be refreshed and printed to pdf.
I figure the simplest way to do this would be to task schedule the workbook to open daily. Then on open it will refresh the data, print it after all data has been refreshed and close the workbook.
I set it up originally without the need to print so I have all the queries set to refresh when opening the file, however when I now try and put the code to print to pdf on the workbook open event it runs before the queries are finished running. (Query notes: queries were created through Microsoft query, and are accessing a MySQL database queries set to refresh when opening the file queries set to enable background refresh).
When I run a group of macros for some reason after the marcro runs and I hit F2 excel crashes.
I have figured out that it is one of the marcros that makes the excel crash. It is only when I run the solver application. The code is below. Any thoughts why hitting F2 after running this marcro would crash the machine?
I have created a macro in xl2000 which was working fine - although through corporate upgrades my machine has now been upgraded with xl2003 and the macro runs for a bit and then crashes on the code below, although it only highlights teh 'rnum'
rnum = ActiveCell.Row
Is there a difference between xl2000 & xl2003?
I cannot make this work in XL2010.
I am opening a second workbook in ReadOnly in the Workbook Open and Closing it in the Workbook BeforeClose.
I don't EVER want to save changes on either workbook.
I need to leave excel open as the macro created a 3rd workbook which the user will need.
[Code]....
I have a very brief question
dlgAnswer = Application.Dialogs(xlDialogOpen).Show
Selection.Copy
Windows("Derivative YK pricing Mod G.xls").Activate
Columns("B:B").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("C5").Select
Application.CutCopyMode = False
Windows("EXPORT1.xls").Activate
ActiveWindow.Close
As you can see, I open a workbook with the dlgAnswer, I was wondering how I could close that opened workbook
Currently I have
Windows("EXPORT1.xls").Activate
ActiveWindow.Close
however the workbook will not always be called export1.xls, I want it to close whichever one I opened earlier.
I have a report I produce where I take an XML file, sort it and run some simple formulas then sort another way and run a few more formulas then update a pivot table. The XML files I'm using have the same headers but vary in length. When I set the macro up for a particular XML file everything works fine. When I import another one (different size) I get the error message:
Run-time error ‘1004’:
Sort method of Range class failed
When I look at the code I see that the range is fixed to the XML file I used when setting the macro up. Is there a way to auto-select the table in the macro so I can run the same macro on any size XML?
I have developed a model for a client which keeps crashing after being used a few times. The crash is major! Excel shuts down and asks to send an error report.
This can happen after being run anywhere from 3 to 20 times. (Clicking a POST button which transfers items to a "database" type sheet).
I developed the model on a PC using Excel 2010. When this was loaded on a Mac, the user tells me that it happened periodically, and I have seen is occasionally on my PC as well.
However, the workbook has now been loaded on a server, with users across the state of Victoria accessing via Sharepoint - most using Excel 2007. Now it is happening very frequently.
I don't think the macro is at fault, it doesn't go into debug. Excel just seems to GIVE UP.
Have you seen this kind of thing before? Is there a way of "flushing buffers" or something so that Excel can keep working?
I am reluctant to post the model because it is full of personnel details, and is 2.5mb.
I have comment blocked the code that causes Excel to crash.
View 4 Replies View RelatedThe macro does numerous things, including deleting the sheet that was active when it is called. If the sheet is not deleted, there is no crash. Otherwise, Excel crashes as soon as the VBA interpreter hits "Exit Sub". The Excel message is simply "Excel Stopped Working". The macro also saves the workbook before finishing, and the saved workbook opens just fine, but it's unacceptable to have to kill Excel and restart it every time (I am not the main user for this spreadsheet). I'm using Excel 2010 on Windows 7.
View 7 Replies View RelatedI'm just wondering if any of you can lend some help on a bug i'm encountering. When I press a button on my worksheet to call a macro that brings up a userform, the excel will crash, and give that "do you want to report this error", close down excel, and reopen on a blank worksheet. Strange thing is, this error is avoided if I save the workbook upon opening it, and then call that macro.
The workbook does not read data from external sources, so i dont think is that problem.
Has anyone encountered this?
I need to know the command to close a workbook. I have used the information found here: http://www.ozgrid.com/VBA/userform-close.htm to disable closing the workbook via the upper right "X", but I need a button that, when clicked, simply closes the workbook instead.
View 2 Replies View RelatedI have a VBA macro for Excel 2007 below that loops through a workbook and deletes a picture (shape) in a range at the top of each worksheet.
The macro works fine until a cell which contains a seemingly unrelated data validation list on Sheets(1) is changed. The macro then repeatedly trips up with a 'Run-time error 1004 - Application defined or object defined error'.
The cell with the data validation is outside of the range in which the shapes are deleted and does not set any of the variables in the macro.
Sub DeleteLogos()
Dim Count As Integer
Dim NumberOfWorksheets As Integer
Dim Logo As Shape
Dim LogoZone As Range
NumberOfWorksheets = Worksheets.Count
For Count = 1 To NumberOfWorksheets
With Sheets(Count)
[code].....