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.
Any Idea's how to fix this?
View 6 Replies
ADVERTISEMENT
Jul 16, 2014
I have a code that opens another WB, and gather data from it. most of the lines look like this:
HTML Code:Â
wb.Sheets("Data").Range("K" & Rows.Count).End(xlUp).Offset(1) = wb2.Sheets(1).Range("M49").Value
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.
View 3 Replies
View Related
Aug 14, 2014
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.
[Code].....
View 2 Replies
View Related
Dec 6, 2008
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 ....
View 14 Replies
View Related
Jun 8, 2009
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.
View 2 Replies
View Related
Apr 3, 2007
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......................................
View 5 Replies
View Related
Jun 14, 2008
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.
View 7 Replies
View Related
Jun 19, 2007
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. ....
View 9 Replies
View Related
Aug 12, 2008
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....
View 7 Replies
View Related
Apr 3, 2008
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......................................................
View 9 Replies
View Related
Aug 8, 2009
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?
View 7 Replies
View Related
Dec 19, 2009
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.
View 8 Replies
View Related
Jul 26, 2009
I have comment blocked the code that causes Excel to crash.
View 4 Replies
View Related
May 29, 2013
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]....
View 1 Replies
View Related
Aug 2, 2009
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.
View 6 Replies
View Related
Aug 3, 2013
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?
View 4 Replies
View Related
Mar 20, 2014
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.
View 6 Replies
View Related
Oct 31, 2011
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.
View 1 Replies
View Related
Dec 20, 2013
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.
View 7 Replies
View Related
Nov 28, 2013
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.
View 1 Replies
View Related
Feb 28, 2012
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)
[code].....
View 4 Replies
View Related
Sep 17, 2010
Excel 2007 crashing when trying to up date a data source within a pivot table?
View 3 Replies
View Related
Feb 3, 2013
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.
View 2 Replies
View Related
Jan 27, 2009
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..
View 2 Replies
View Related
Sep 9, 2009
I am now trying to combine 2 sections of code but being a complete VBA novice I'm stuck, where should the second Sub go so that it works,
View 4 Replies
View Related
Aug 1, 2013
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
[Code].....
View 1 Replies
View Related
Sep 22, 2009
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.
View 2 Replies
View Related
Nov 20, 2008
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.
View 10 Replies
View Related
Nov 28, 2008
I am trying to add code to a sheet with one code already on it.
Each of these 2 sets of codes work just fine alone, but I do not know how to make them both work on the same sheet.
A working sheet with the code I want to add is attached. The code I would like to add is on the same sheet (pasted on the the page). (See attachment)
View 11 Replies
View Related
Mar 28, 2009
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.
View 6 Replies
View Related
