Your VBA/Automation Headquarters!
Most people realize that Excel can be automated. There are many resources all over the internet that provide information on Excel VBA. You'll quickly find the popular names. Meanwhile, we offer some general information and troubleshooting.
Excel crashes when you open it, or when you try to copy cells, or when you try to save the file? The first thing you will want to do toward troubleshooting your Excel program is read about Startup Folders. Also read about File Corruption issues if the program only crashes when using one or two files that you use.
We assume that you keep your PC clean of unnecessary temporary files (all kinds) and that the PC and the hard drive are healthy. To ensure this, check out the Clean Your PC instructions we provide.
Next, if you can get Excel to open, try running Detect and Repair from the Help dropdown menu in either Excel 2000 or Excel XP.
If none of the above works for you, continue reading.
Edit the Registry
Many people will tell you not to go into the registry without adult supervision. And that's true. However, if you follow these steps EXACTLY, you will not only be fixing your registry, but backing it up at the same time.
You must first CLOSE Excel.
Start-->Run and type:
Hit your Enter key. As you might browse folders in Windows Explorer, browse to the appropriate path:
For Excel 97:
For Excel 2000:
For Excel XP:
If you are unable to find this folder, or do not understand how to find it, then you should stop here and get support over the phone or in person.
Once you have found the path, right click the Excel folder, hit Rename and rename it to OldExcel. Hit Enter, and then exit the Registry Editor; and then relaunch Excel. If this still has not resolved the problem, keep reading.
Uninstall, Erase, and Reinstall Office
Uninstall using Add/Remove programs in the Control Panel. Start-->Settings-->Control panel. Select Office, and then choose Remove.
Run Eraser 97 or 2000, as appropriate. To-date, there is no Eraser program for Office XP. Instructions and downloads can be found at:
Reinstall the program using the disks. If none of these options have helped, there is likely some other software on your system that is interfering with the operation. Some anti-virus programs have settings that affect the operation of programs.
It is very important to understand the XLSTART folder. One day, and you may have heard of this before, you open Excel and it attempts to open a whole bunch of files--and not just Excel files either. Why does this happen?
When you launch Excel, it opens all of the files in two different folders. It first looks in the XLSTART folder on your hard drive. It also looks in the folder you designate under Tool-Options, General tab as the Alternate startup folder. Generally, people confuse the Alternate startup folder as their Default file location. Unfortunately, if you enter an Alternate startup folder of C:\My Documents, Excel launches and attempts to open all of the files in there, including non-Excel files. If you have hundreds of files in there, it could take some time to get past all of the errors you'll receive.
Why does it open these files? Many people use Excel VBA and/or recorded macros in their daily routines. A file named Personal.xls or any other file, can be created to store these macros and make them available for use with any file. To make these macros available to all files, they must be loaded--or opened when Excel is launched. See more on macros under Excel VBA.
Most people use Excel for the purpose of having a database of information or for financial purposes. We recently saw a document from a very large corporation that contained financial reports, one on each page. What a mess! How much easier it would be to do all of your calculations, and data entry on one sheet. Then, create a Word document and copy the cells from Excel and EditàPaste specialàPaste link so that your reports are updated when you change your Excel data.
The absolute best file layout for using Excel and its features is as follows:
This layout allows you to use the features of Excel as they were intended to be used. Many people never discover the features because their files are not set up in this manner, the feature doesn't work the first time they try it, and they forget about it. Don't stop trying! You'll find that the things you're doing manually every day might easily be accomplished with one of Excel's features. If you're unsure, ask at the Forum!
No-Nos and Workarounds
These are the rules to follow to avoid problems with Excel's features:
Cannot find the file . . .
Seems it's becoming more and more popular. There are other causes for this, but go to Tools-Options, General tab and make sure Ignore other applications is NOT checked.
Excel opens a bunch of files when I launch it . . .
Go to the top and read about the XLSTART folder under Startup Folders.
Cursor selects cells when I'm not holding my mouse button . . .
It could be your wheel mouse. Just click on the wheel. Otherwise, you need to Clean your PC.
Cannot shift non-blank cells off of the
worksheet error . . .
Keep the file clean by taking these steps on each worksheet. Go to your first completely blank column to the right of your data. Hold your Shift and Ctrl keys and hit your right arrow key until it hits column IV. From the menu hit Edit-Clear-All. Go to the first completely row at the bottom of your data. Hold your Shift and Ctrl keys and hit your down arrow key until it his row 65536. Hit Edit-Clear-All. Save the file now, because it will not recognize the clearing of those cells until you do.
Do not copy a sheet and then make a copy of the copy, and a copy of the copy of the copy. Instead, make a boilerplate sheet and always copy that one sheet. We have not yet determined if this is an issue only in Excel 97, so better to be safe than sorry. Otherwise, you may get the Sheet111111111111111111 error. I've looked for the fix recently at Technet and have been unable to locate it. If you've already got this error, and if it's possible to open it, save the file down to Excel 5.0, close it, reopen, and save as your current version again. Or open it in Excel XP, which often fixes the document for you--even after you have been unable to open it in lesser versions.
If you're getting illegal operations or other errors in a specific file, copy the worksheets into a new workbook. Right-click any sheet tab, choose select all sheets (that is, if you have more than one sheet). Right-click a sheet tab again, and hit Move or Copy. Choose New book from the dropdown, checkmark the Make a copy box. Hit Ok. If your file is excessively large, you may need to copy one sheet at a time using New book the first time, save the new book, then copy the remaining sheets to the newly saved file. You may find that one sheet in the workbook may need to be recreated.
Creating drawing objects: Create these in another file. When your drawing is complete, group all of the drawing objects and paste the one object into your *real* file. Having many drawing objects pieces floating around in MS Office files seems to be a real source for corruption.
Inserting a mixture of MS Office drawings, graphics, and other objects all in one file appears to cause corruption.
After inserting graphics, save your file and check its size. Save your file to another name. Now select each graphic, cut it, and hit Edit-Paste special-As a picture. See if this reduces file size, while retaining good resolution on your graphics. This is almost always helpful in reducing the size of files, but on rare occasion has actually increased file size.
With files that are used often, possibly by many users, and have been around for years, you may want to *standardize* the formatting. Formatting one cell at a time creates one format that is saved in the file. Formatting a group of cells at one time creates one format. So it makes sense to format as many cells that have the same format as you can at one time. For instance, select any columns that contain dates (hold the ctrl key to select noncontiguous ranges) and format them as dates. Do the same with numbers that are formatted alike. As often as possible, do formatting on entire columns and/or rows; and select as many as you reasonably can at one time, then do the cell formatting. For instance, your columns A, D, and F may all contain currency. Select column A by clicking on the A. Hold your ctrl key and click on D and F. Then format the cells. Cell formatting includes font name and type, size, borders . . . everything.
This image breaks down the Vlookup. If a real file will help you to understand better, go to the Free Downloads page for a free download that explains the *dreaded* Vlookup formula.
This is so easy. This method works whether your data list is on the same worksheet or not; but you must have your data in the same workbook.
Create your list of choices. I like to have the very first option be Select One or Choose One or Choose from Dropdown--we'll use "Select One" for our example. Then, you can put conditional formatting on the cell so if the cell is equal to "Select One", the fill is Yellow, which lets your user know that a selection needs to be made in that cell. Once choice each in cells going down a column or across a row. For our example, we'll put out list on Sheet2. We can put a bunch of lists in different columns here. You might want to give your lists headings for your reference.
Now, select the cells that contain your list--do not select the heading if you've given it one. From the menu, hit Insert-Name-Define. Give it a one-word name (no spaces allowed)--for our example, we'll name it MyList. (You could instead type that name into the box above cell A1, as shown in the picture.)
Next, click on the cell where you want your dropdown to appear. From the menu, choose Data-Validation. Choose List in the Allow box. In the Source box, type "=MyList". If you forget the = sign, it will NOT work.
Hit OK. Now, when you click on that cell, you'll get a dropdown from which to choose.
Select the cell that contains the dropdown, and hit Format-Conditional formatting. Set it so that if your cell value is equal to the first item in your list, that the pattern fill should be yellow.
That will bring attention to the cell and prompt your users to select an item:
In our example, we placed all of our dropdown lists data on sheet2. We can now hide sheet2 so that nobody sees our ugly data lists.
Copyright© 2003 - 2010 The Office Experts and Tickling Keys, Inc.
Access®, Excel®, FrontPage®, Outlook®, PowerPoint®,
Visio®, and Word®, are registered trademarks of the Microsoft Corporation