Excel Crashes When Adding Code To Workbook Via VBA
Apr 9, 2009
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.
However i need it to gather the sum: M49+M50, how can i do this without importing both values and then adding them in a 3rd column, like i am currently doing.
Adding some additional code to prevent someone from saving the workbook under a different name. Currently if someone tried to enter data after the allotted time period, it would allow them to in any "unlocked" cells. When exiting or trying to save the file with this new data, they will get a message stating something to the effect that this is a read only file would you like to save as another name, which would allow the user to circumvent what I'm trying to accomplish. The end result would be they'd need a new spreadsheet.
I have a userform that I enter information into fields and I would like to created a command button that will insert the information into the workbook ....
As 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.
Hello, I'm using VBA to produce a combo box for any cell using data validation list.
This way the cell auto completes as well as displays as many fields as desired...I now have it functioning so that when you click on any cell that contains data validation list assigned to it, it will create the combo box in the cell for you...It functions great most of the time, however the sheet is used all day long and on a average 10 hour day it causes Excel to crash about 3-5 times. ....
I have a set of userforms, two of which are big with 4 pages and many buttons, labels and textboxes that run or are involved in code being run from user input to the userform.
For some reason certain codes are cauing excel to fully crash, executing these codes will make excel crash each time and the only way to prevent it (that I have found) is to open the userform in VB editor and then close VB editor again, the code will then work perfectly for a while and then it will start crashing again (it is normally a few saves and open/closes later that this occurs).
Logically thinking, opening the userform in VB must reset something that gets corrupted or set incorrectly....
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?
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 have a UserForm appear when the user opens my file. It prompts them to enter some data. If they Close (Terminate) the Window, I have a MsgBox appear telling them they must enter data - and allow them to go back or exit. When they opt to go back the UserForm reapears but Excel freezes and I have to force quit.
Here is the code for my UserForm, including the Sub for when the User clicks OK (CommandButton1) and the Sub for when the User terminates the window:
VB: Private Sub CommandButton1_Click() Dim Blank As Integer Dim Subgroup As Integer [code]....
When an Excel document crashes, I get the option to recover the document the next time I open Excel. However, if it is a read-only file, I don't get this option.
Is there any way around this? I usually work in read-only documents, saving my changes to new documents. If the read-only file I'm working in crashes.
Ok so I have an extensive worksheet that pulls a lot of data from the web, 47 connections to be exact. So on my work computer that runs excel 2007, the data refreshes instantly and I can see all the numbers flying through and changing as the sheets refresh.
However, my personal computer running excel 2010 hangs up with any type of data refresh, not just this sheet in particular. This computer has 8 GB of ram so I don't think that would be the issue?
Does excel 2010 have an issue with data refresh from the web?
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 use Excel 2010, 64-bit-version with Windows 7. I have one workbook, where whenever I create a formula that references to one specific sheet, the Excel crashes and cannot be quitted even with Task Manager. I have to restart the whole system.
I do not have any worksheet events in either sheet. This is the same workbook that crashes if all cells are selected from the top-left corner of the worksheet.
I tried by deleting unnecessary COM add-ins but that didn't work.
The 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.
I have 2 excel files (both .xlsx) and in the first file i am creating a reference to cells in the 2nd file using SUMIFS function. The 2nd file has 275,000 rows of data. I can create the formula correctly, however when i save the file, it just crashes every time (says not responding). If i create simple reference (=A275000) then its fine so im not sure what the issue is.
I 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)
How to amend existing code in a workbook that is used by many different users. I am working in Excel 2003. The existing code copies data from certain cells in the active worksheet (which is password protected) into a new worksheet and saves that new worksheet to a temp folder, sends it to a particular email address using outlook and closes it, returning the user to the first workbook. You will see that it also unhides then rehides columns of the first worksheet during this process so that the user never has to see them. Problem here is that none of the users are using outlook anymore and I would prefer that this new spreadsheet that is created just be saved to the users desktop. I have found bits and pieces of what I needed on the web, but I can't seem to put it all together (I usually end up being sent to the debugger just short of having the newly created spreadsheet saved to desktop). Here is the existing code:
Private Sub CommandButton1_Click() Range("A2:G2").Select Selection.ClearContents[code]....
Essentially what I need to do is circumvent the whole email bit and save the spreadsheet that is created to the users desktop instead of a temp folder.
I use excel 2002 but some of my office are on 97, i want to add a small workbook open event code which works for me but debugs for the others?? The code is basically, go to a tab, on that tab and that range sort..
My company has files that are already in use. I don't know too many details about how they work, but somehow saving the file will screw it up and my boss has to go back and reset something or other to correct it. Obviously it's connected to some other software somewhere. The code below will block Save and Save-As. BUT how do I get the file to hold onto the code without actually saving the file after the code is added (since the file shouldn't be saved)?
VB: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If SaveAsUI = False Then Cancel = True
I was wondering if there's a way to make the numbers in a column over 50 (as an example) green and numbers less than 50 red. (The numbers in this column are the sums of other cells, but I guess that doesn't really matter.)
This way when there's a lot of things going on you can see what's what without filtering or rearranging.
I want to display value from cell A11 to J11, and when the button is clicked, I need to transfer the value to underneath the last value on sheet “Results” leaving 1 space. So in my example it would go into row 28. On other occasions there may only be 1 value for example on results, if this was the case the row from Additional parts would have to display in row 7.
Then if I were to add another value in additional parts I would then want this to display in row 30 in my example. So again leave a space and add the line.
I've added the following code into macro i have assigned to the button on this attached worksheet which deletes any row where the cell in the A column is blank.
