Excel Crashing On End Sub Line?

Aug 11, 2012

I made an Excel/VBA solution for a company in which userforms were created and manipulated programatically, including their code modules. They would then be deleted so next time they were created there wasn't naming errors.

They acted as data input forms which would have an input control for each column of data on the sheet.

There was one method which would create the form and then call another to add code to it's code module.

In one scenario, if the user would do data entry on one sheet and then immediately after do data entry on the same sheet using the data entry forms.

When this occurred something peculiar happened, after the method which added the code to the code module executed excel would crash. By putting a MsgBox to pop up just before the End Sub statement and having another in the calling function immediately after the procedure call. It became evident that the sub adding code to the module was not exiting correctly and causing the program to crash.

In essence the program was crashing when End Sub was executed

View 1 Replies


ADVERTISEMENT

Excel Crashing When Copying Tab?

Apr 28, 2014

My Excel file crashes whenever I attempt to make a copy of a tab, within the same workbook, and then save. This problem only occurs with its existing naming convention. In other words, the file crashes with the name "Cash Flow Model 5 Year Plan". If I rename the file to "Copy of Cash Flow Model 5 Year Plan" then it works perfectly, with no issues copying a tab within the workbook. The file size is not too large, as this issue never occurred when the model was twice the size.

View 1 Replies View Related

Outlook Keep Crashing Via Excel?

Nov 21, 2013

I have to send out emails to all of my staff with their new login ID's and PW's and other misc information one by one to each user. Reason being is that all ID's, PW's and other information pertains to that specific user and includes sensitive information.

I know there's a VBA script I can use to send out the emails, but the script I found online keeps crashing my Outlook and requires it to go through my personal inbox which takes 15 minutes every time.

I have the pertinent information split up into different columns/cells.

A1: email
A2: subject
A3: body (ID, PW, verbage)
A4: attachment link (if required)
A5: if I could have Outlook automatically stamp each email with my signature that'd be awesome as well.

View 4 Replies View Related

Excel Crashing When Viewing VBA Code

Dec 27, 2012

I have a semi-large macro which I have been creating on and off for a few months now. The file will fundementally be used by people with little knowledge of Excel, hence I force the user down certain alleyways depending on what they have previously done with the aid of veryhidden sheets and such like.

Upon opening the file, some code is ran to hide all of the sheets in the file apart from the home page. This is a pain when I am still working on the code though, so a portion of it is commented out. When I uncomment the "on error.. - next sheetIn" so that the file runs as it would be used, the code itself works perfectly and hides all of the sheets apart from the home page. Unfortunately, I can't then view the VBA code as it instantly crashes Excel (and any other Excel file I currently have open). This happens whether I click the Visual Basic button in the Excel ribbon, whether I try and access it through the design mode on a control, pressing Alt+F11 or even opening up another spreadsheet containing code and attempting to click on the broken macro after viewing the VBA code for the working spreadsheet.

VB:
Sub workbook_open()
'stop screen flickering whilst running the code
Application.ScreenUpdating = False

[Code]....

The only way currently to get back into the code is to open the spreadsheet up with macros disabled and recomment the code, but obviously this loses the function of the code.

View 3 Replies View Related

Excel Crashing On Refresh Of Data

Sep 12, 2013

I have a report that uses external data to feed a Pivot Table.

I noticed that the data source had been turned off automatically by Excel and turned it on and added the file location to the trust centre.

I also set the data to refresh when the workbook is opened.

I saved the file and then tried to refresh the data which caused Excel to crash. Of course now I can't open the file either because when it attempts to refresh itself on opening that causes Excel to crash as well.

Addendum: I have removed the Trusted Location which has caused Excel to disable the connection again. This has solved the crash on opening but I still can't refresh the data.

View 3 Replies View Related

Function Code Crashing Excel

Apr 16, 2014

I've been trying to put together a function in excel which will make it simpler and a bit clearer to produce the coefficients of trend lines in cells. I want to produce an excel function like SLOPE and INTERCEPT but for exponential, power and logarithmic trendlines. For example, I can produce the coefficients for an exponential trendline like this:

Code:
=SLOPE(LN(K2:K11);J2:J11)
=EXP(INTERCEPT(LN(K2:K11);J2:J11))

Ideally, I want to be able to do so without the need to convert the terms with LN function, and be able to replace it with a new function that deals directly with an X Range and a Y range

I've produced the following code:

Code:
Function PowerSlope(YRange As Range, XRange As Range)

For Loops = 1 To YRange.Count
YRange.Value2(Loops, 1) = Log(YRange.Value2(Loops, 1))
Next Loops

PowerSlope = Application.WorksheetFunction.Slope(YRange, XRange)
End Function

However, this causes Excel to crash and shut down (not the macro itself, but the entire program). I can't even get the function to run to a break point in the first line before the crash happens. I'm able to remove the FOR ... NEXT loop and use the function to directly return the result of the linear slope, so I'm confident the issue is not in that part of the code.

View 4 Replies View Related

Excel Crashing When Inserting / Deleting New Columns?

Jul 21, 2014

I'm building a bookkeeping workbook that is only currently 800kb with TWO cells that have data validation and no cells have conditional formatting. There are a fair few formulae but they are all simply SUM, SUMIF and CONCATENATE.

View 1 Replies View Related

Excel 2010 :: Keeps Crashing And Not Responding Or Saving?

Mar 9, 2012

I have a genuine copy of office pro 2010. All works fine except excel!

It seems to be mainly on one workbook I have brought over from open office and on a mac! It wont save due to errors it won't tell you about? It then stops responding or crashes? Wont save as etc....

I have repaired, I have even deleted and downloaded a new office pro genuine and installed and doing the same so i guess it has to be the file?

BUT it seems to work on another laptop we have, same file, same excel????

The only thing i seem to be able to find is in the permissions it has an "unknown account"? Also under the file menu where it allows you to check the file it states something about conditional formatting i have used and not being compatible BUT i cant for the life of me find that in the menus to get rid?

View 10 Replies View Related

Excel 2010 :: Crashing When Pasting Formulas As Values

Nov 8, 2013

I recently received an .xls book which I then saved as .xlsx (I'm using 2010). There are just under 8,000 rows and 20 columns. File Size 1MB.

The only formulas in the sheet are the ones in a column which I inserted and copied down for all 8,000 rows. Nothing too complicated: no arrays or anything. The sheet calculates fine.

I am simply trying to copy and paste these formulas as values (into the same cells), though at every attempt Excel crashes. I tried on smaller sets of the column and just got it to work for a few hundred rows, though it struggles with any more than that.

I opened a different workbook of mine, and tried the same operation on twice as many cells containing complicated, lengthy array formulas and the action completed instantly.

There is no Conditional Formatting in the book, no code, no 'last cell' issue, no Named Ranges, no external links.

I have even copied the data to a new workbook, then copied the text of just one of the formulas over into this book, added an equals sign, copied down and recalculated, then tried to paste as values again. Still crashes.

Formula:

=IF(AND(N3>1,ROWS($1:1)<>MATCH(M3,$M$3:$M$7979,0)),"Exact Duplicate","")

is far more resource-hungry than I thought, though if that were the case, wouldn't the issue be during calculation (which, as I said, is fine) and not during a paste attempt? No, it can't be this.

View 1 Replies View Related

Excel 2007 :: Crashing Using Named Range In Conditional Formatting?

Feb 10, 2014

I have one simple (but large table). It has dates across the top (formatted in hh format). I would like the associated table to format according to the day and also to format differently when there is a public holiday. So I have built a table with the holidays and named the relevant cells as "Holiday_Valid".

I have the following formula in the conditional formatting;

[Code]....

Where D11 has the current day in question and Holiday_Valid is a list of public holidays. Since there is a cell for each hour of each day I am using "int()".

There is a second conditional format to format Sundays differently as below;

[Code] .....

My problem is that these both work well....but then after a few minutes the whole sheet crashes with those dreaded "Trying to recover your data" and "Excel will restart" etc.

I have removed references to named ranges and so far - so good....but this means putting the validation table in the same sheet as the main table. In the past I have been able to use named ranges (albeit not in such convoluted formulae), but now it seems that it is not working any longer.

When I open the recovered sheet, all the conditional formatting has been removed and the message from the repairs is that there was some invalid conditional formatting.

Extensive web searches did show some issues with conditional formatting using names ranges....especially with frozen panes....which I need use with a sheet this big.

View 4 Replies View Related

Excel 2010 :: Insert A Line Every 4th Line?

Nov 29, 2013

I have a spread sheet with product codes on and 5 different prices types but they have only pulled through 4 times so need to insert a 5th line for each product code

View 2 Replies View Related

Crashing Between 2003 & 2007

Jul 28, 2009

I've been putting together an automated spreadsheet that has external data sources to SQL. I have been having some strange problems with it between versions of Excel. Unfortunately, the company's standard is 2003 but quite a lot of the company use 2007. I have been developing this report on 2007, the server that runs the automated version of the spreadsheet runs 2003 (and cannot be upgraded beyond 2003 as it is running Server 2000!).

The template opens fine on my 2007 machine and all development work has been run on my machine including the downloading of data. The VBA script runs something like

refresh datasources ........

View 14 Replies View Related

Crashing Error/sheets Add

May 2, 2007

I have a large model, and it sometimes starts having very strange errors. The workbook sometimes becomes corrupted, giving a:

"Microsoft Excel has encounted a problem and needs to close. We are sorry for the incovenience"

"AppName: excel.exe AppVer: 10.0.6823.0 ModName: kernel32.dll
ModVer: 5.1.2600.2945 Offset: 00012a5b"

error when I try to close the spreadsheet. One of the sheets can have a lot of data (up to 10,000 x 150) and the error will also occur if I try and delete the sheet, or clear the entire sheet. Also, the model runs vba code, which at one point deletes the data sheet and adds a new sheet. However, sometimes the sheets.add functionality stop working, i.e. when the code runs the sheets.add line, nothing happens. I don't know if the errors are related.

View 3 Replies View Related

Hide Rows Macro Crashing

Feb 14, 2014

I had this code working perfectly. However in the last 24 hours it has started freezing on me. Here is the code:

' This code will dynamically hide or unhide rows based on whether a row contains a transaction

Sub hideRows()
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False

Sheet1.Unprotect Password:="University"

[Code] .....

I call the code into a Worksheet_Change module on a particular sheet (Sheet1). It hides rows between 11 to 1503, but if I innocently select these rows, the spreadsheet freezes on me & I have to kill Excel. I'm guessing I need to add code to counter these do nothing events?

View 1 Replies View Related

Unusual Crashing Due To Userform Or Comboboxes

Jun 4, 2008

The spreadsheet is essentially a VBA GUI that validates information entered before writing it to a sheet in the workbook. There is a button on the first sheet that opens the GUI, and when the workbook is first used the GUI opens and runs fine. After adding a few rows using the GUI, saving the workbook, and then reopening it, attempting to open the GUI by clicking on the button will cause Excel to crash (and no errors are given). To compound the problem, it is not possible to find the issue by using the debugger, as the GUI runs fine as soon as VisualBasic is opened. I've tried to narrow it down by using MsgBoxes to find the approximate location where the form crashes, and it seems to happen when the .ListIndex property of a ComboBox is set inside the UserForm_Initialise method. I've played around for days trying to narrow it down further, removing .ListIndex statements as much as possible without breaking the entire thing.

View 2 Replies View Related

Code Loops Through Range Instead Of Crashing On Second Iteration

Apr 6, 2014

Not come across this issue before where the Code works once then fails to iterate!

In attached file 140405 Loop not working.xlsm, for each cell in C4:C27 on the Inventory Sheet, the Code should:

Look in the Data Input sheet.

Find the Item Number in Col B corresponding to the number in Col A of the Inventory Sheet.

Find the quantity on the Data Input sheet corresponding to that number.

Copy the quantity into Col H of the Inventory sheet.

The Code works for the first row then throws error message "Run Time error 13" Type Mismatch!

VB:
Sub OFFLOAD()
Dim Dest As Range, Dest1 As Range, oCell As Range
Set Dest = Sheet2.Range("C:C")
Set Dest1 = Sheet2.Range("B:B")

Sheets("INVENTORY").Select

[Code] .....

View 2 Replies View Related

7-zip Command Line Via Excel?

Feb 10, 2012

I've got an Excel file that uses 7-zip. It can execute a command line that will use 7-zip to go to a folder and zip each of the folder's files into its own zip file, placing the zips in a single, specified destination folder. I can specify whether to look in the source folder, or to also look in all of the source folder's subdirectories.

Question is, how do I tell 7-zip to just zip ONE file, and how do I tell 7-zip to zip a SPECIFIC LIST of files that I name (instead of looking through a whole folder)? how to use command line...

Code:
Sub a_zip_with_sub_directories()
Shell "cmd /c for /r ""C:Users aiDocumentsmonthly reportsFinal Copies of Monthly Reports"" %i in (*.*) do ""%ProgramFiles%7-Zip7za.exe"" a -tzip ""C:DATA aiipped\%~ni"" ""%i"""
End Sub
Sub a_zip_no_sub_directories()
Shell "cmd /c for %i in (""C:Users aiDocumentsmonthly reportsFinal Copies of Monthly Reports*.*"") do ""%ProgramFiles%7-Zip7za.exe"" a -tzip ""C:DATA aiipped\%~ni"" ""%i"""
End Sub

View 2 Replies View Related

Drawing Line Segments In Excel With VBA

May 8, 2012

I'm trying to draw a diagram with many nodes connecting several line segments.

Each has a colour attribute which I'd like to show on the graph.

How can I plot it to-scale using VBA in excel?

TypeCoord1Coord2Colour
Node0 0 Yellow
Line0 1 Black
Node1 1 Red
Line1 3 Blue
Node3 3 Purple
Line3 9 Orange

View 3 Replies View Related

Creating Excel Line Chart?

Mar 10, 2014

My chart is coming in blank when I set up a line chart. This is my data that I selected and then pressed chart. 1997

63.9%
1998
63.2%

[Code].....

View 9 Replies View Related

Excel 2010 :: Dynamic Line Chart

Jul 12, 2012

I would like to plot the 1st dynamic line chart as shown in [URL] ..... but i never write any excel macro 2010 before, any sample of this?

View 4 Replies View Related

Delete Part Of A Text Line With Excel?

Jan 29, 2014

I have a text document with 1000 lines. All the lines are in this format

important< %#$ other words
other important thing < %Z$different words
some important text < $# some other words

I want to know if is such a excel filter that will delete all the characters starting from "<" until the end of the line.

So, the output of this 1000 lines will look something like this:

important
other important thing
some important text

View 4 Replies View Related

Excel Adds Extra Line On Some Files

Jun 20, 2014

I have a really strange issue on one machine. The end-user gets Excel reports emailed to him. When he opens certain ones, there is an extra space (almost a line without borders) on certain rows. This only happens on some of the files he receives

AND

This behavior is only on his system. On all other recipients' machines the spreadsheets do not have the extra space on some rows. It does not matter if he opens the attachment within Outlook or saves and opens in Excel from the save location.

I have tried resetting the defaults and that does not work.

View 2 Replies View Related

Excel 2007 :: Combining Two Line Charts

Feb 4, 2012

I have two line charts in Excel 2007.

One chart has the values going down from upper left to lower right
The other chart has the values going up from lower left to upper right

Is there anyway to merge these charts to see if they intersect at all?

View 1 Replies View Related

Excel 2010 :: Add Comma To The End Of Line In Cell?

Jan 25, 2013

i have 5000+ addresses with no punctuation in them, just seperated by line breaks. i need to be able to add a delimiter (semi-colon, colon, full stop, any will do) to the end of every line of the address, not the cell; in order to extract the info using 'Text To Columns' in Excel 2010

View 9 Replies View Related

Excel 2010 :: Loop Through Every Line On Sheet?

Sep 30, 2013

How can I get this code to loop through every line on sheet?

Code:

Selection.Copy
Windows("SheetA.xlsm").Activate
Cells.Find(What:="Part Number", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

[Code] ..........

View 2 Replies View Related

Excel 2007 :: Dynamic Colors In Line Chart

Dec 9, 2013

I have a line charts with values above and below zero.

Im looking for a way to colour the steps below zero in one colour, and above zero in another colour - without doing it manually.

Using Excel 2007.

View 1 Replies View Related

Excel 2003 :: Add Horizontal Line To Stock Chart?

Feb 25, 2011

I am using the the stock chart and I would like there to be a horizontal line across the chart with a y-axis of the opening price. In the end I want this to be in VBA but how to do this manually I should be able to put it in loop of data that will produce many charts with a horizontal line starting at the day opening.

I have tried to add a new series to the chart but I'm using the Candle stick stock chart and the new series comes out as a vertical not horizontal line. I read about using a secondary horizontal axis but all the tutorials I find are in Excel 2003 and they donlt line up well with Excel 2007.

I found this link on charts with horizontal lines but again they are in 2003 and I'm using a candle stick stock chart not a scatter chart. [URL]

View 6 Replies View Related

Import HTML Table To Excel Without Line Breaks?

Jan 31, 2012

I have an html table (3 columns) that has a decent amount of formatting in each column. It uses the <P> attribute for seperating content blocks in each cell along with Lists (LI's) and OL's. It breaks the content on each <P> and <LI> attribute and places each item on a seperate line. I want the content to all be in one cell. I've heard about using a css style to make it stay in the same cell, but it doesn't seem to do anything at all. I've tried:

HTML Code: 

<style type="text/css">
br {mso-data-placement:same-cell;}[code]....

any way to import this table into excel without all the line breaks?

View 7 Replies View Related

Excel 2010 :: Adding Horizontal Line In Chart?

Oct 11, 2011

I want to a horizontal line in a chart. There are only two figures with me. One I want to display as a column chart. The other as a horizontal line chart. (I can use both excel 2003 or 2010, whichever is suitable)

View 2 Replies View Related

Excel 2010 :: Line Graph Isn't Charting Lines?

Feb 23, 2012

I have been trying to chart temperature differences over 10 day's time for six cities using a line graph. For some reason, the lines are all clustered at the bottom of my graph, with values of 0. My spreadsheet is accurate, without any blank cells. I'm using Excel 2010.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved