|
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. Dropdown Lists (Data Validation)
Excel TrainingNow you can learn Excel at your own pace! Simply purchase these downloadable courses when you're ready. Let's talk about some great ideas: Suppose your administrative assistant doesn't really have enough to fill their day. I agree there's probably not many around, but most companies have a slower time of year. During that time, buy this training for your assistant. Tell them to spend X hours per week doing training during this slow period. The likelihood of someone learning everything there is to know about any software application from ANY course materials is unlikely. They'll learn lots of things, however, and they'll remember that what they're trying to accomplish can be done, and they'll have the reference materials to show them how. Do you know that the reason most people don't use software to its fullest is because they are simply not aware of the features? Go ahead and buy the training. We're sure it's worth every penny! All 3 Levels of Excel Training for One Low Price Excel TroubleshootingExcel 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 RegistryMany 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: regedit 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 OfficeUninstall 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: Office 97: 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. |
 |
Column headings (call them field names if you like) are in Row 1 |
|
First record is in Row 2 |
|
No completely blank rows in your data |
|
No completely blank columns in your data |
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:
|
Use more than one row for column headings: To insert a "paragraph return" within a cell, use Alt-Enter on your keyboard. You may need to adjust row height to accommodate the text. |
|
Report title on the worksheet instead of in the header. If you must have the title on your worksheet, increase the height of Row 1 and place the title in a textbox using the Drawing toolbar. Place the textbox above your column headings in row 1. |
|
Place headings at the top of columns, not in rows. For instance, A2 has "Branch 1", and then B2 through B7 have address and contact information about Branch 1. A8 has "Branch 2", and then B8 through B13 have address and contact information about Branch 2. This is a bad setup for use with Excel. Though it might print out nice, you will have a hard time getting, for instance, a branch phone list. Instead, put each item of information under its own column headings. You can then sort by any column you wish, and using Mail Merge with Word will allow you to display this information in any manner that you want. |
|
Do not use Excel for documents that contain a lot of text. Use Word tables instead. They work very similar to Excel columns, but allow the freedom of entering and viewing a lot more text. If you are convinced that you need Excel because you need to total a column, think again. Calculations can also be done with Word, especially simple ones. Decide what is the priority for the document: having it look nice and text that is easy to manipulate versus extensive calculations. |
Common Excel Issues
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 . . .
-or-
Excel prints extra blank pages . . .
This occurs when Excel believes that all of your rows or columns are filled. If you constantly use a file over and over, then delete the contents of cells by hitting your delete key, eventually you'll get this error message. You may have deleted the cell's contents, but the cells still contain formatting information. This can also cause file bloating.
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 blank 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.
Too many formats error . . .
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.
File Corruption
Our belief that a file is corrupt isn't always true. Sometimes, we just have a dirty file: dusty in the corners, cobwebs on the ceilings, and the like. Here are the steps you can take to keep file corruption from occurring and, if you can still open it, get the corruption out.
Precautions
|
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. |
The Dreaded Vlookup
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.
Dropdown Lists
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.
Tips:
-
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 - 2004 The Office Experts
Microsoft Office®,
Access®, Excel®, FrontPage®, Outlook®, PowerPoint®,
Visio®, and Word®, are registered trademarks of the Microsoft Corporation