I have a multi-tabbed spreadsheet that keeps crashing when I try to set up a reference from one tab to another tab in the same spreadsheet (e.g., =Sheet2!B1) . The spreadsheet does NOT have any macros in it although my Personal.xls DOES have numerous macros that I have been using for over 6 years with no problems. In any ase, when I start Excel without loading personal.xls, so that NO macros are loaded, the spreadsheet still causes Excel to crash when I try to set up the reference to another tab.
I have a file with about 30 sheets and a size of 14 meg. When the file is saved by using the save button it seems to save. When the file is closed the Microsoft crash message appears. The changes seem to save and if you choose not to have Excel attempt to repair the file everything seems fine.
The other odd behavior the file has that is if you run a for loop on any tab it takes about 3-4 minutes to run. If you copy the tab to another book and run the same code it runs in 3-4 seconds. Any ideas?
The situation is a Workbook_Activate macro is conflicting with a worksheet module which is a worksheet_calculate that also runs a macro when there is a query refresh from another book. How do I get the workbook activate macro to run before the worksheet one activates? Right now everything crashes.
This file contains the printing options of a larger userform. But I am having problems with the print preview crashing.
I'd click one of the printing options on the userform, this would bring up the print preview of that sheet.
And when I try to "test" its stability by going crazy on the view next/ previous page on preview, it'd crash. This is especially when I've got the screenupdating set to false.
Just wondering if anyone else can varify the crash/ suggest what maybe its cause?
I have problems with my excel crashing all the time when I try to run my code. It seems to happen in a loop where I open a .txt file and pull data from it, then close it, and open the next one...
There are no error code, just a message that Excel has stopped working and it will be closed, and then it is forced to restart. Just a mess!
This is my loop code:
Code: Option Explicit Dim Desktop As String Sub Open60only() Desktop = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator Dim which_folder As String, which_file As String, which_string As String, tick As String Dim lastrow As Long Dim wb1 As Workbook, wb2 As Workbook
I have a piece of code in one of my Macro's, if I run the macro and don't let this piece of code run everything works fine, when I let this piece of code execute the excel file keeps running until I go to save, at which point I get a message saying "Microsoft office excel has encountered a problem and needs to close. We are sorry for any inconvenience".
Code is as follows:
If MsgBox("Copy and hardcode this scenario?", vbYesNo) = vbYes Then Calculate Application.DisplayAlerts = False
what excel can process without having the program crash. Our company is using an old dos program to store payroll information (don't ask -they won't change it). Anyway - I have the ability to use pervasive software to pull the information into excel.
The problem here - is that the information is stored line by line. And in order for me to get useful information out of it, I need to sort those lines by date and a task code.
I have an add-in procedure that creates a new workbook and adds code to the new workbook's ThisWorkbook object (a Workbook_Open event handler). However, the .AddFromString and .InsertLines methods are causing the accursed "Excel has encountered a problem and needs to close" error. I've stripped the code down to rule out possible syntax errors in the generated code, and it still crashes.
If Not wbTemplate Is Nothing Then Set objVBProj = wbTemplate.VBProject If Not objVBProj Is Nothing Then strCodeText = "Private Sub Workbook_Open()" & vbCr strCodeText = strCodeText & "MsgBox " & Chr(34) & "test" & Chr(34) & vbCr strCodeText = strCodeText & "End Sub ' Workbook_Open"
With objVBProj.VBComponents("ThisWorkbook").CodeModule .DeleteLines 1, .CountOfLines .AddFromString strCodeText End With End If ' access vbproject End If ' workbook exists Set objVBProj = Nothing
I have an add-in that has one function in it. We plan on adding more, but not until we figure out what's happening. The one function is a Select- Case statement...send the function an alpha code and it returns a numeric account number based on the alpha. About 20 "cases" in total. The add-in loads fine and the function works fine from a spreadsheet cell. However, we're getting differing results when we try to access the function from VBA code. I've been able to duplicate this several times on three different computers on our network.
The VBA code will work fine, several times and then on the 4th or 5th attempt it will cause Excel to crash ==> "Microsoft Office Excel has encountered a problem and needs to close. We are sorry for the inconvenience." If I " recover" the crashed file, comment out the line to call the function, run the program again it works. Then I can un-comment the line, re-run the program and it works fine again for 4 or 5 more attempts. I've tried running the Repair tool already. I've also copied the code out of the spreadsheet and started a new file. The same errors occur...works 4 or 5 times and then crashes the program. The add-in and the spreadsheet exists on networked drives.
I'm working with Excel 2000 on Windows XP machines. I have the following
Private Sub soInputBox_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) If KeyCode = vbKeyReturn Then PushScreenUpdateState False clearAllData loadAllData Worksheets("Main Screen").Activate PopScreenUpdateState End If End Sub
Private Sub loadAllData() soInputBox.Enabled = False End Sub
The idea is this: the user can either type the value into the text box (soInputBox) and then click a button, or type the value into the box and then hit enter. They will both do the same thing; set in motion all the functions that load a bunch of data, etc.The code for the event handler for hitting enter is shown above. Inside of the loadAllData function, I disable the text box. What seems to be happening is that after the soInputBox_KeyDown function exits, Excel crashes. If I comment out the line to disable the text box, Excel doesn't crash. My question is this: is this a known problem, and is there a known workaround? I would like it so that the text box is disabled so that the value that was input cannot be changed accidentally during the rest of the programs execution, so if there's a way to change the control to be unmodifiable instead of disabling it that would be a reasonable alternative.
Examples: This is the userform with the 'No Answer' boxes added into the user interface but not added to the code - this one doesn't crash excel: [url]
and this is the userform with the 'No Answer' boxes added into the code - this one will crash excel (but only when you open it directly from excel, it doesn't crash it if you test it out from vb): [url]
I am having a strange error occur. I am trying to debug a function in an add-in I developed, but whenever I try to hover over a value or add a watch, the whole application crashes. It runs without crashing when I don't try to debug it.
I have a fairly big excel spreadsheet with about 2 userforms at the moment. One Userform (the main one) has a multipage control with about 12 pages. Now here is the weird part. I open up the excel document and this main user form loads up simply with this code
Sub Auto_Open() Load Userform1 Userform1.show End Sub
This works fine the form loads. Now on this form there is a multipage control with about 12 pages as mentioned above. Now everything works the way I want it to eg the text boxes, combo boxes, command buttons etc all work and link with the worksheets fine just the way they should...But if I reference or try to use a couple of the controls in any way excel crashes. It doesn't give any errors or anything it just crashes and then I get a prompt saying do you want to send the error report blah blah. I have narrowed it down to 3 command buttons and 1 combo box so far that this happens on. If I do the most basic of commands on any of these controls such as something as simple as msgbox problemcombobox.visible...
I have having alot of frustation with a big group of Macro's I have written, all the macros work and run fine most of the time but they crash Excel on occasion and the only way I have been able to fix it is to go into Visual Basic and opening the project up (it is password protected) but not making any changes, after doing this it seems to magically stop crashing, for a while, then doing it again.
I can't work out how or why that would make a difference, I dont expect you to try and work out a problem in the code or anything like that cause it is too big and would be unreasonable of me to ask,
What I would like to know is if anyone else is aware of this sort of issue, why it happens and if there is any good cure for it?
I am running excel 2003 if that also makes any difference.
Have a code to clear contents on one of my worksheets and it continually causes excel to freeze and forces me to exit the program. When the button is selected it takes approx. 30-45 seconds to clear the data and then excel freezes up. The code is as follows:
Sub() Sheets("Sheet1").Range("B2:D16000").ClearContents End Sub
The data in the range B2:D16000 comes from another macro that copies and pastes values to this sheet. I am also working on the mac 2011 version.
I'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.
I'm in the process of writing/editing a macro, but after I run the macro once, the Expressions in the Locals Watch window disappear, and if I try to run it again it produces an "Excel has encountered an error and needs to close". The macro probably doesn't make much sense out-of-context, but perhaps there's a glaring error that's causing this. A guess - possibly related to the Error Handling in the vba? (1st time I've used this in a macro).
Option Explicit Sub Compare() Dim strNaspK As String Dim strCtryK As String Dim intCtryRev As Integer Do Until ActiveCell. Offset(1, 0) = "" ActiveCell.Offset(1, 0).Select strNaspK = ActiveCell strCtryK = ActiveCell.Offset(0, 2) On Error Goto ErrJump:........................
Basically, I've got two sets of data on different sheets, with NASP ID and country being variables. Just trying to import the data from the 'Comparison' sheet, if both the NASP id and country matches that on the first sheet, established by the variables.
I am trying to sort a bunch of data by a custom list from left to right and after running the macro successfully excel crashes upon saving. I have tried this on different computers, resaved, started from scratch and it always crashes so there must be something with the macro that is causing this.
here is my macro:
Edit: It seems that the last line of code is causing my problem. I have started from scratch and gone though all the code one at a time and saved after each run. everything is fine until the "Application.DeleteCustomList Application.CustomListCount" is run. after that excel crashes when saved.
-2147417848 automation error the object invoked has disconnected from its clients
Unfortunately, I can't tell where the error is occurring because everytime it happens excel crashes or at least stops responding. I tried waiting it out (about 20 minutes) but no luck. the only thing I can do is force the program to shut down through the task manager.
The program is designed to pull information from finviz.com (a financial site), filter the data. pull the next round of data and run the filters again. there are a total of 8 queries and i think it gets hung up during the 6th or 7th run through.
Stepping through the code does not seem practical since each query might pull as many as 300 rows of data that is evaluated in a for/next loop (thats a lot of F8s).
activesheet.printpreview , Excel crashes if there are no printers installed on the machine. How can i check for printers before calling the printpreview, or else stop the program from crashing?
I have been using Erik's fantastic "Force Enable Macros" code (exactly as it appears)...
force enable macros & ASK to save changes (SOLUTION)
...with the desired results (and comfort) under Excel 2003.
The code works under Excel 2007. However, if there is another workbook open at the sametime (regardless of whether Workbook two has macros or not), I get an Excel "crash";
Code: Microsoft Office Excel has encountered a problem and needs to close.
[ ] Recover my work and restart Microsoft Excel There is also a Event ID 7001 in the Event Viewer. I have run Excel diagnostics and there are no issues.
I am having problems with an Excel workbook I am working on. I have a sheet that has a number of controls added to the top of it. Two of these controls are listboxes. My problem is, if I am in the listbox navigating through it, and I accidently move the Mouse Wheel, Excel crashes and I get the usual, "Send Error Report" form.
I have seen that there are other people having this problem and that there are a number of solutions out there but my problem is a bit different. I have a few forms with listboxes on them, and while mouse wheel scrolling doesn't work, it certainly doesn't crash Excel. It only seems to happen when I have a control permanently added to the sheet. Is there any event handler for mouse wheel scrolls or anyway to disable them through VB?
I have created several reports containing pivot charts with slicers, pivot tables and data sets. Total file size is around 5MB. Need to do an update in my pivot table Like adding an additional grouping on one of my dimensions. As soon as I start doing so the Microsoft Excel stopped working error message pops up and my file crashes! Removing existing groupings seems for some reason to be impossible as well: nothing happens when I do this. Ahow to solve this without having to rebuild all my reports?
So i got a few macros on a worksheet and one of them is simply :
Sub Pr_Removefilter() ActiveSheet.ShowAllData End Sub
Ive worked on the excel a couple of times, everything fine. Then it started crashing upon saving (after changes made that had no possible connection to the macro, on another sheet). After a few painfull trials and errors i located the problem to be this macro button. I erased it, created a new one with the EXACT macro and it works. Why?..
Excel will hang up as if it's gone into a continuous loop (Although no looping macros are active) when you open or close a user form. The task bar in Excel will flash Ready Calculate. Visual basic reports no code is running but excel either crashes or becomes very unresponsive. I have to exit and reopen excel in order to fix the problem . It also doesn't happen at any one specific point it can vary. I have various VLOOKUPs around the workbook and userforms display the correct information. I use the INDEX formula in the control source of textboxes. I also use the offset formula to make sure DTPICKER displays correctly.
I am using 'Option Explicit' at the start of all my code. I have tried a 3rd party code cleaner. No visual Basic references are MISSING
It is quite standard to create links between workbooks, and generally I do this by inserting an "=" sign in the cell I want data to appear in for Workbook 1, I then open workbook 2 where the desired data is and click on the cell housing the data I want.
I just went to do this, and upon putting the = sign in the cell I want in workbook 1 and clicking in workbook 2 nothing happens except my cursor is now in workbook 2 and the = sign just remains alone in the workbook 1 cell.
If, however, I put an = sign in a cell in workbook on and then click another cell in that sheet or a cell in another worksheet but in the same workbook a proper link is created, so the problem seems to be isolated to links to other workbooks.
I've attached a basic excel file with an example of the problem I need to solve. As you can see, if you select "No" to "Do they have a pet?" then conditional formatting blocks out the next field. The problem with that is, it still allows you to enter a value into the "Type" field which is skewing my results. Is there a way to allow a value in "Type" ONLY if there is "Yes" in "Do they have a pet?". I would also like it to delete the value in "Type" if I later select "No" instead of just covering it up. Is this possible? I'd like to avoid the scripting route if at all possible but I'm not sure it is...
Also, I have two linked spreadsheets, one pulls the totals off the other. However, If I add rows into the original it does not update the total formulas in the second sheet. Is there a way to make it do this automatically?
I am working with excel2003. I have to different workbooks in which on is a Bill of lading that is used to show the items being shipped. The other workbook has a list of the items that we ship.
On the BOL I want to be able to type in the "L" number (or I.D. number) in D14, D19, D24, D29. By typing in the L-number I want it to automatically bring up the product name into cells E14, E19, E24 and E29. With the exact name of the item it will help prevent employee error. I want it to also bring over the exact weight over from the flavor list in cell range C5:C28.
I have the FlavorCodes range with all the L-numbers on the BOL in cell range U9:U48. I thought I had it set up correctly but when I select the L-number in the drop down list in cells E14, E19, E24 and E29 I get error code #NA. I thought I defined the cell range but it is still not working.
I have a problem and wanted to know if there was an easier way to create links to various worksheets. I have a folder with 59 worksheets. The names of the Worksheets are the weeks of the year(example LA-01-01-09) I have another workbook with 13 worksheets one for every month and a sheet that totals the whole year up.
What I want to know is how can I create a link to everyone of those weekly sheets to go to my monthly sheets,so when data is entered into the weekly sheet the monthly sheet will update automatically? The weekly sheets are setup exactly the same. I'm looking only to pull row 3 columns c:d:f:g:h:i over to my monthly sheet.
I have an example of my weekly sheet and monthly sheet.
I have a Form that when the 'SUBMIT' button is clicked, I would like to validate the information and then write it to a 'MASTER SHEET'. However, the validation has gotten lengthy and I have opted to put the validation code in a separate Module to keep my general code a little cleaner.
PROBLEM:
The original code would kick you back to the form if something was wrong via a 'END SUB', but as now the SUB is on a different Module, it isn't working.
Here is the pertinent code on the general code sheet: ....
