Close All Workbooks Quickly

Category: General | [Item URL]

If you find yourself with many workbooks open at the same time, you may be interested in a "hidden" command that will close all workbooks in one fell swoop.

The trick is to press Shift while you click the File menu. When you do so, the Close command turns into the Close All command. When you select this menu item, Excel will close all of the currently open workbooks. If any of them have not been saved, you'll get the standard prompt asking if you want to save your workbooks.

Restrict Cursor Movement To Unprotected Cells

Category: General / General VBA | [Item URL]

Q. The formulas in my worksheet use values in several input cells. I've unlocked the input cells and protected the sheet so the user can't change the formulas. Can I set things up so the cell cursor moves only to the input cells?

Yes. You've already unlocked your input cells and ensured that all other cells are locked. By default all cells are locked, but you can change that by using the Protection tab of the Format Cells dialog box. Select the cells to be changed and choose Format, Cells. In this case, the input cells are unlocked and all other cells are locked.

Protect the worksheet in Excel 97 by using Worksheet, Protect or Tools, Protection, Protect Sheet (you can specify a password to keep others from "unprotecting" the sheet). Once the sheet is protected, press Tab to move the cell pointer to the next unlocked cell.

This does not prevent the user from selecting unlocked cells using the cursor keys. To make those cells unselectable, change the worksheet's EnableSelection property. Select View, Toolbars, Control Toolbox to display the Control Toolbox toolbar. Click the Properties button to display the Properties box for the worksheet, then click the cell labeled "xlNoRestrictions" and use the drop-down list to change the EnableSelection property to xlUnlockedCells. Close the Properties box. As long as the worksheet is protected, users cannot select the locked cells on the worksheet.

This procedure does not save the EnableSelection property setting with the workbook. To create a simple macro that turns this setting on when the workbook is opened, press Alt-F11 to activate the Visual Basic Editor. Locate your workbook name in the Project window, and double-click it to expand its listing. Then double-click the item labeled ThisWorkbook and enter the following VBA code:

Private Sub Workbook_Open()
  Worksheets("Sheet1").EnableSelection = xlUnlockedCells
End Sub

This macro executes whenever the workbook is opened, and sets the EnableSelection property of Sheet1 to xlUnlockedCells. The technique can be circumvented by changing the EnableSelection property to its default value (xlNoRestrictions). Few users know about this dodge, however.

Change The Color Of Worksheet Tabs

Category: General | [Item URL]

Q. Can I change the color of the worksheet tabs in my workbook?

Many users find it helpful to color-code the sheet tabs to make it easier to identify specific sheets. If you use Excel 2002 or later, right-click on the sheet tab, choose Tab Color, and then select the color for the tab.

If you're using a previous version of Excel, you cannot change the tab colors.

Making An Exact Copy Of A Range Of Formulas

Category: General / Formulas / General VBA | [Item URL]

Assume that A1:D10 on Sheet1 has a range of cells that contain formulas. Furthermore, assume that you want to make an exact copy of these formulas, beginning in cell A11 on Sheet1.  By "exact," I mean a perfect replica -- the original cell references should not change.

If the formulas contain only absolute cell references, it's a piece of cake. Just use the standard copy/paste commands. But if the formulas contain relative or mixed references, the standard copy/paste technique won't work because the relative and mixed references will be adjusted when the range is pasted.

If you're a VBA programmer, you can simply execute the following code:

With Sheets("Sheet1")
 .Range("A11:D20").Formula = .Range("A1:D10").Formula
End With

Following are step-by-step instructions to accomplish this task without using VBA (contributed by Bob Umlas):

1. Select the source range (A1:D10 in this example).
2. Group the source sheet with another empty sheet (say Sheet2). To do this, press Ctrl while you click the sheet tab for Sheet2
3. Select Edit - Fill - Across worksheets (choose the All option in the dialog box).
4. Ungroup the sheets (click the sheet tab for Sheet2)
5. In Sheet2, the copied range will be selected. Choose Edit - Cut.
6. Activate cell A11 (in Sheet2) and press Enter to paste the cut cells. A11.D20 will be selected.
7. Re-group the sheets. Press Ctl and click the sheet tab for Sheet1
8. Once again, use Edit - Fill - Across worksheets.
9. Activate Sheet1, and you'll find that A11:D20 contains an exact replica of the formulas in A1:D10.

Note: For another method of performing this task, see Making An Exact Copy Of A Range Of Formulas, Take 2.

Creating A Database Table From A Summary Table

Category: General | [Item URL]

Many users are familiar with Excel's pivot table feature, which creates a summary table from a database table. But what if you want to perform the opposite operation? This document describes how to create a database table from a simple two-variable summary table.

The worksheet below demonstrates. Range A1:E13 contains the original summary table, and columns G:I shows a 48-row database table derived from the summary table.

How to do it

The solution to creating this "reverse pivot table" is to use a pivot table! The steps below are specific to the example data shown, so you'll need to modify them slightly to work with your data.

Part 1: Creating a pivot table

1. Activate any cell in your summary table
2. Choose Data - PivotTable and PivotChart Report (the menu command may vary, depending on the version of Excel).
3. In the PivotTable dialog box, select the Multiple consolidation ranges option, and click Next.
4. In Step 2, choose the I will create the page fields option and click Next.
5. In Step 2b specify your summary table range in the Range field (A1:E13 for the sample data) and click Add. Click Next.
6. In Step 3, select a location for the pivot table, and click the Layout button.
7. In the Layout dialog box, you will change the default layout in the diagram. Drag both the Column button and Row button away from the diagram. This will leave the diagram with only a data field: Sum of Value. The dialog box should look like the figure below.
8. Click OK and then Finish to create the pivot table.

Part 2: Finishing up

At this point, you will have a small pivot table that shows only the sum of all values:

1. Double-click the cell that contains the total (outlined in yellow, above). Excel will create a new sheet that displays the original data in the form of a database table (see the figure below).
2. The column headings will display generic descriptions (Row, Column, and Value), so you'll probably want to change these headings to make them more descriptive.

A VBA Macro to do it

If you do this sort of thing on a regular basis, you may prefer to use a VBA macro. Just copy the VBA code to a VBA module. Then activate a cell in your summary table and execute the ReversePivotTable macro. This macro uses simple looping -- no fancy pivot table tricks.

Solving Common Setup Problems

Category: General | [Item URL]

This document describes the solution to several common problems involving Excel setup.

Excel crashes when it starts

When Excel is started, it opens an *.xlb file, which contains your menu and toolbar customizations. If this file is damaged, it may cause Excel to crash when it it started. Also, this file may (for some reason) be very large. In such a case, this may also cause Excel to crash. Typically, your *.xlb file should be 500K or smaller.

If Excel crashes when it is started, try deleting your *.xlb file. To do so:

1. Close Excel
2. Search your hard drive for *.xlb. The filename and location will vary.
3. Create a backup copy of this file and then delete the file.
4. Re-start Excel. Hopefully, Excel will now start up normally.

NOTE: Deleting your *.xlb file will also delete any toolbar or menu customizations.

Many documents open automatically

If Excel automatically opens lots of files at start-up, here are two things to check:

1. Your XLStart directory. Files stored in your XLStart directory are opened automatically when Excel starts. Move the files in this folder to a different folder.
2. Your Alternate startup directory. Select Tools - Options. In the Options dialog box, click the General tab. Locate the setting called At startup, open all files in. If this field is not empty, delete its contents.

Excel's menus are messed up

If your menus change, or if there is a delay before all menu items are not listed, you need to make a change.

1. Select View - Toolbars - Customize.
2. In the Customize dialog box, click the Options tab.
3. Place a checkmark next to the item labeled Always show full menus.

NOTE: This is, without a doubt, the dumbest option ever created! Why would anyone want their menus to change?

Commands are missing from the menu

If the steps in the preceding section don't solve the problem, you can reset Excel's menu bar:

1. Select View - Toolbars - Customize.
2. In the Customize dialog box, click the Toolbars tab.
3. Scroll down the Toolbars list and select Worksheet Menu Bar.
4. Click the Reset button

NOTE: This will return the menu to its default state, and destroy any menu customizations that may have been done.

Excel displays extraneous menu commands

Another common problem is extraneous menu items. For example, you may have used an add-in that added a new menu item to the Tools menu. And, for whatever reason, the add-in did not remove that menu item. To remove the menu item:

1. Select View - Toolbars - Customize
2. When the Customize dialog box is displayed, access the extraneous menu item and "drag it away." That will delete it.
3. Click OK to close the Customize dialog box.

Double-clicking an Excel file does not work

Normally, double-clicking an XLS file starts Excel and opens that file. If this doesn't work for you, you'll need to re-register Excel. Do do so:

1. Close Excel
2. Click the Windows Start button
3. Click Run, to display the Run dialog box
4. Type the following, and click OK:
   

   excel / regserver

5. You'll see a message box that display the progress. When the message box closes, Excel should be back to normal.

You get a macro warning when no macros exist

When you open a workbook, you may be prompted to enable or disable macros --even though no macros exist in the workbook. Press Alt+F11 to activate the Visual Basic Editor. Locate your workbook in the projects window:

1. If the workbook contains any VBA modules (for example, Module1), delete the module. Even an empty VBA module may trigger the macro warning.
2. Examine the code modules to ThisWorkbook, and the code module for each Sheet (for example, Sheet1). Make sure that these modules do not contain any macro code. You cannot delete these code module, but they must be empty to avoid the macro warning dialog box.

You get an erroneous "file is being edited by" message

When you open a file that is in use, you'll get a message that tells you the file must be opened in read-only mode. In some cases, you may get this message even though the file is definitely not in use. This can be caused by an Excel crash, in which the file was not released. The only way around it is to re-start Windows.

Numbers are entered with the wrong number of decimal places

For example, entering 154 appears as 1.54 in the cell. Somehow Excel's fixed-decimal mode was turned on. To return to normal:

1. Select Tools - Options to display the Options dialog box.
2. Click the Edit tab
3. Remove the checkmark from the Fixed decimal option.

Of course, this feature can be useful when entering some types of data, but most of the time, you'll want to keep the fixed-decimal mode turned off.

Numbers, not letters appear in the column header

Normally, Excel columns are labeled with letters. If they actually appear as numbers, you can change it back to the default:

1. Select Tools - Options to display the Options dialog box.
2. Click the General tab
3. Remove the checkmark from the R1C1 reference style option.

Getting A List Of File Names

Category: General | [Item URL]

Many users are surprised to discover that Windows does not provide a direct way to get a list of file names contained in a directory. This tip describes how to create such a list in a text file, which can then be imported into Excel.

To generate a list of file names, you'll need to use a DOS command typed in a DOS command window. To open a DOS command window:

1. Click the Windows Start button
2. Click Run
3. Type "cmd" (no quotes) and press Enter. if "cmd" doesn't work, use "command".

You'll get a window like the one shown below.

Next, you need to type a DOS command to generate the file list. For example, if you would like to generate a list of all files in the root directory of drive D, type the following at the command prompt and press Enter:

dir d:\

To list the files in a particular directory, add the directory name after the drive:

dir d:\my files\

The file names will be listed in the window. Usually, you'll want these files to be sent to a file. To redirect the output to a file, use the > character and specify a file name. For example, to send the file names to a text file named filelist.txt in the root directory of drive C, use this command:

dir d:\ >c:\filelist.txt

If you would like the file list to include the files in all subdirectories of drive D, use the /s switch:

dir d:\ /s >c:\filelist.txt

The directory listing will contain lots of additional information. To get the file names only (bare format), use the /b switch:

dir d:\ /s /b >c:\filelist.txt

To find out about other options available for the DIR command (such as including file dates and times), type this command:

dir /?

After the text file is generated, you can import it into Excel by using the File - Open command.

NOTE: If you need do this on a regular basis, you may be interested in this article from Microsoft, which describes how to create a batch file that lists file names in Notepad.

CommandBar Calculator

Category: General / CommandBars & Menus | [Item URL]

A companion file is available: Click here to download

I created an add-in that displays a new CommandBar that functions as a simple calculator. It may not be the most useful tool (it's limited to only the four basic mathematical operations), and I'll be the first to admit that it's fairly ugly. But it does demonstrate some useful VBA programming techniques.

The code is too lengthy to list here, but the VBA project for the add-in file is not protected so you can examine it.

NOTE: This add-in does not work with Excel 2007.

Installation:

1. Download the Toolbar Calculator add-in
2. Start Excel and select the Tools - Add-Ins command. 
3. In the Add-Ins dialog box, click the Browse button and locate calctoolbar.xla.
4. Click OK
5. The toolbar will appear
6. Select Tools - Toolbar Calculator to toggle the toolbar on and off.

Spreadsheet Protection FAQ

Category: General / General VBA | [Item URL]

The topic of "protecting" a spreadsheet receives a great deal of attention in the Excel newsgroups. This document provides answers to some common questions.

Excel provides three primary ways to protect information in a spreadsheet:

• Worksheet Protection
• Workbook Protection
• VB Project Protection

NOTE: This document was written prior to the release of Excel 2007.

Worksheet Protection

Questions in this section deal with protecting the contents of cells and objects on a worksheet.

How do I protect a worksheet?

Activate the worksheet to be protected, then choose Tools - Protection - Protect Sheet. You will be asked to provide a password (optional). If you do provide a password, that password will be required to unprotect the worksheet.

I tried the procedure outlined above, and it doesn't let me change any cells! I only want to protect some of the cells, not all of them.

Every cell has two key attributes: Locked and Hidden. By default, all cells are locked, but they are not hidden. Furthermore, the Locked and Hidden attributes come into play only when the worksheet is protected. In order to allow a particular cell to be changed when the worksheet is protected, you must unlock that cell.

How do I unlock a cell?

1. Select the cell or cells that you want to unlock.
2. Choose Format - Cells
3. In the Format Cells dialog box, click the Protection tab
4. Remove the checkmark from the Locked checkbox.

Remember: Locking or unlocking cells has no effect unless the worksheet is protected.

How do I hide a cell?

1. Select the cell or cells that you want to unlock.
2. Choose Format - Cells
3. In the Format Cells dialog box, click the Protection tab
4. Add a checkmark to the Hidden checkbox.

Remember: Changing the Hidden attribute of a cell has no effect unless the worksheet is protected.

I made some cells hidden and then protected the worksheet. But I can still see them. What's wrong?

When a cell's Hidden attribute is set, the cell is still visible. However, it's contents do not appear in the Formula bar. Making a cell Hidden is usually done for cells that contain formulas. When a formula cell is Hidden and the worksheet is protected, the user cannot view the formula.

I protected my worksheet, but now I can't even do simple things like sorting a range. What's wrong?

Nothing is wrong. That's the way worksheet protection works. Unless you use Excel 2002 or later.

How is worksheet protection different in Excel 2002 and later?

Excel 2002 and later provides you with a great deal more flexibility when protecting worksheets. When you protect a worksheet using Excel 2002 or later, you are given a number of options that let you specify what the user can do when the worksheet is protected:

Select locked cells	Delete columns
Select unlocked cells	Delete rows
Format cells	Sort
Format columns	Use AutoFilter
Format rows	Use PivotTable reports
Insert columns	Edit objects
Insert rows	Edit scenarios
Insert hyperlinks

Why aren't these options available in earlier versions of Excel?

Good question. Only Microsoft knows for sure. The limitations of protected worksheets have been known (and complained about) for a long time. For some reason, Microsoft never got around to addressing this problem until Excel 2002.

Can I lock cells such that only specific users can modify them?

Yes, but it requires Excel 2002 or later.

How can I find out more about the protection options available in Excel 2002 or later?

Start with Excel's Help system. If you're a VBA programmer, you may be interested in this MSDN article that discusses the Protection object.

Can I set things up so my VBA macro can make changes to Locked cells on a protected sheet?

Yes, you can write a macro that protects the worksheet, but still allows changes via macro code. The trick is to protect the sheet with the UserInterfaceOnly parameter. Here's an example:

ActiveSheet.Protect UserInterfaceOnly:=True

After this statement is executed, the worksheet is protected -- but your VBA code will still be able to make changes to locked cells and perform other operation that are not possible on a protected worksheet.

If I protect my worksheet with a password, is it really secure?

No. Don't confuse protection with security. Worksheet protection is not a security feature. Fact is, Excel uses a very simple encryption system for worksheet protection. When you protect a worksheet with a password, that password -- as well as many others -- can be used to unprotect the worksheet. Consequently, it's very easy to "break" a password-protected worksheet.

Worksheet protection is not really intended to prevent people from accessing data in a worksheet. If someone really wants to get your data, they can. If you really need to keep your data secure, Excel is not the best platform to use.

So are you saying that protecting a worksheet is pointless?

Not at all. Protecting a worksheet is useful for preventing accidental erasure of formulas. A common example is a template that contains input cells and formulas that calculate a result. Typically, the formula cells would be Locked (and maybe Hidden) the input cells would be Unlocked, and the worksheet would be protected. This helps ensure that a novice user will not accidentally delete a formula.

Are there any other reasons to protect a worksheet?

Protecting a worksheet can also facilitate data entry. When a worksheet is locked, you can use the Tab key to move among the Unlocked cells. Pressing Tab moves to the next Unlocked cell. Locked cells are skipped over.

OK, I protected my worksheet with a password. Now I can't remember the password I used.

First, keep in mind that password are case-sensitive. If you entered the password as xyzzy, it won't be unprotected if you enter XYZZY.

Here's a link to a VBA procedure that may be able to derive a password to unprotect the worksheet. This procedure has been around for a long time, and is widely available -- so I don't have any qualms about reproducing it here. The original author is not known.

If that fails, you can try one of the commercial password-breaking programs. I haven't tried any of them, so I have no recommendations.

How can I hide a worksheet so it can't be unhidden?

You can designate a sheet as "very hidden." This will keep the average user from viewing the sheet. To make a sheet very hidden, use a VBA statement such as:

Sheets("Sheet1").Visible = xlVeryHidden

A "very hidden" sheet will not appear in the list of hidden sheets, which appears when the user selects Format - Sheet - Unhide. Unhiding this sheet, however, is a trivial task for anyone who knows VBA.

Can I prevent someone from copying the cells in my worksheet and pasting them to a new worksheet?

Probably not. If someone really wants to copy data from your worksheet, they can find a way.

Workbook Protection

Questions in this section deal with protecting workbooks.

What types of workbook protection are available?

Excel provides three ways to protect a workbook:

• Require a password to open the workbook
• Prevent users from adding sheets, deleting sheets, hiding sheets, and unhiding sheets
• Prevent users from changing the size or position of windows

How can I save a workbook so a password is required to open it?

Choose File - Save As. In the Save As dialog box, click the Tools button and choose General Options to display the Save Options dialog box, in which you can specify a password to open the file. If you're using Excel 2002, you can click the Advanced button to specify encryption options (for additional security). Note: The exact procedure varies slightly if you're using an older version of Excel. Consult Excel's Help for more information.

The Save Options dialog box (described above) also has a "Password to modify" field. What's that for?

If you enter a password in this field, the user must enter the password in order to overwrite the file after making changes to it. If the password is not provided, the user can save the file, but he/she must provide a different file name.

If I require a password to open my workbook, is it secure?

It depends on the version of Excel. Password-cracking products exist. These products typically work very well with versions prior to Excel 97. But for Excel 97 and later, they typically rely on "brute force" methods. Therefore, you can improve the security of your file by using a long string of random characters as your password.

How can I prevent a user for adding or deleting sheets?

You need to protect the workbook's structure. Select Tools - Protection - Protect Workbook. In the Protect Workbook dialog box, make sure that the Structure checkbox is checked. If you specify a password, that password will be required to unprotect the workbook.

When a workbook's structure is protected, the user may not:

• Add a sheet
• Delete a sheet
• Hide a sheet
• Unhide a sheet
• Rename a sheet
• Move a sheet

How can I distribute a workbook such that it can't be copied?

You can't.

VB Project Protection

How can I prevent others from viewing or changing my VBA code?

If you use Excel 97 or later... Activate the VB Editor and select your project in the Projects window. Then choose Tools - xxxx Properties (where xxxx corresponds to your Project name). In the Project Properties dialog box, click the Protection tab. Place a checkmark next to Lock project for viewing, and enter a password (twice). Click OK, then save your file. When the file is closed and then re-opened, a password will be required to view or modify the code.

Is my add-in secure?

The type of VB Project protection used in Excel 97 and later is much more secure than in previous versions. However, several commercial password-cracking programs are available. These products seem to use "brute force" methods that rely on dictionaries of common passwords. Therefore, you can improve the security of your file by using a long string of random characters as your password.

Can I write VBA code to protect or unprotect my VB Project?

No. The VBE object model has no provisions for this -- presumably an attempt to thwart password-cracking programs. It may be possible to use the SendKeys statement, but it's not completely reliable.

Extended Date Functions

Category: General / VBA Functions | [Item URL]

A companion file is available: Click here to download

Many users are surprised to discover that Excel cannot work with dates prior to the year 1900. I create an add-in that addresses this deficiency. The Extended Date Functions add-in (XDate) allows you to work with dates in the years 0100 through 9999.

When the XDate add-in is installed, you can use any of the following new worksheet functions in your formulas:

• XDATE(y,m,d,fmt): Returns a date for a given year, month, and day. As an option, you can provide a date formatting string.
• XDATEADD(xdate1,days,fmt): Adds a specified number of days to a date. As an option, you can provide a date formatting string.
• XDATEDIF(xdate1,xdate2): Returns the number of days between two dates.
• XDATEYEARDIF(xdate1,xdate2): Returns the number of full years between two dates (useful for calculating ages).
• XDATEYEAR(xdate1): Returns the year of a date.
• XDATEMONTH(xdate1): Returns the  month of a date.
• XDATEDAY(xdate1): returns the day of a date.
• XDATEDOW(xdate1): Returns the day of the week of a date (as an integer between 1 and 7).

These are all VBA functions.

Applications:

The XDate add-in is particularly useful for genealogists and others who need to perform simple calculations using pre-1900 dates. The figure below, for example, shows the XDATEYEARDIF function being used to calculate ages.

Requirements:

The XDate add-in requires Excel 97 or later.

Limitations:

Be careful if you use dates prior to 1752. Differences between the historical American, British, Gregorian, and Julian calendars can result in inaccurate computations.

Note:

My Power Utility Pak also includes the XDATE functions. However, they are not packaged in an add-in. Rather, you can add the functions directly to the VBA project for your workbook. As a result, you can distribute the workbook without a dependent add-in.

Documentation:

Complete context-sensitive online help is included.

Installation:

Installation is a two-step process:

1. Extract the files
   Download and execute the xdate.zip file. Extract the files into any directory.
2. Install the add-in
   Start Excel and select the Tools - Add-Ins command. In the Add-Ins dialog box, click the Browse button and locate xdate.xla (the file you extracted in Step #2). Click OK.

You can type the functions manually, or use Excel's Paste Function dialog box. To access the Paste Function dialog, click the Paste Function button, or select Insert - Function.  The XDate Functions are listed in the 'Date & Time' Category. When a function is selected in the Paste Function dialog, press F1 to read the online help.

Page 3 of 3 pages
[Previous page]