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
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.
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.
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.
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.
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:
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.
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.
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?
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.
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.
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.
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
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
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.
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?
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.
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
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.
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
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]
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:
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)
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.
