Old-Style Menus In Excel 2007

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

Here's a quick way to display the old-style menu in Excel 2007 or later. Execute this VBA procedure, and you'll get a toolbar that contains the old Excel 2003 menu commands.

Sub MakeOldMenus()
   Dim cb As CommandBar
   Dim cbc As CommandBarControl
   Dim OldMenu As CommandBar

'  Delete it, if it exists
   On Error Resume Next
   Application.CommandBars("Old Menus").Delete
   On Error GoTo 0

'  Create an old-style toolbar
'  Set the last argument to False for a more compact menu
   Set OldMenu = Application.CommandBars.Add("Old Menus", , True)

'  Copy the controls from Excel's "Built-in Menus" shortcut menu
   With CommandBars("Built-in Menus")
     .Controls("&File").Copy OldMenu
     .Controls("&Edit").Copy OldMenu
     .Controls("&View").Copy OldMenu
     .Controls("&Insert").Copy OldMenu
     .Controls("F&ormat").Copy OldMenu
     .Controls("&Tools").Copy OldMenu
     .Controls("&Data").Copy OldMenu
     .Controls("&Window").Copy OldMenu
     .Controls("&Help").Copy OldMenu
   End With

'   Make it visible. It appears in the Add-Ins tab
   Application.CommandBars("Old Menus").Visible = True
End Sub

After you execute this macro, the new menu appears in the Add-Ins tab:

The menu isn't perfect. A few of the command don't work, and the list of recent files in the File menu just shows placeholders.

Getting A List Of Files Names - Another Method

Category: General | [Item URL]

In a previous tip, I described method of putting a list of file names from a directory into an Excel range. Today I discovered another method that's much simpler. It uses the File protocol in a Web browser.

  • Note: This method does not work with Internet Explorer. I tested it with Firefox and Chrome, and it works fine with those browsers.

It's pretty simple:

  1. Enter the directory path in your browser's address bar. For example, g:\music.

    The browser will convert this path into a URI (Uniform Resource Identifier). For example, g:\music is converted to file:///g:/music/. In addition, the files in that directory will be displayed as hyperlinks, and you can traverse the directory by clicking on directory names. Here's how the file listing appears in Firefox:

  2. Press Ctrl+A to select all of the text, and then Ctrl+C to copy it.
  3. Activate Excel, select a cell, and press Ctrl+V to paste.

    The filenames will be pasted as hyperlinks with wrapped text. You might prefer to use Paste Special (with the Text option) rather than Ctrl+V. You'll get a three-column range like this (copied from Firefox):

Google's Chrome browser is actually better for this task because it doesn't precede the file names with "File:" and it puts the date and time in a single cell.

This technique works with a single directory. If you need to get a list of files in multiple directories, you must repeat the procedure for each directory.

For more flexibility, use the Filename Lister utility in my PUP add-in.

Clearing The Text To Columns Parameters

Category: General / General VBA | [Item URL]

Have you ever imported a CSV file, or pasted data into a worksheet, only to find that Excel split up your data incorrectly? If so, the culprit is probably the Text To Columns feature. Here's Step 2 of the wizard that's used to split a single column of delimited data into multiple columns.

In this case, three delimiters are specified: tab, comma, and colon.

This is a very useful feature, and I use it a lot. The problem is, Excel tries to be helpful by remembering these settings for subsequent CSV imports and paste operations. Sometimes remembering these settings really is helpful, but often, it's not. To clear these delimiters, you must display this dialog box, clear the settings, and click Cancel.

If you're importing or pasting via a macro, there's no direct way for your macro to check these settings or reset them. The solution is to "fake" a text-to-columns operation. The procedure below does that, with the effect of clearing all of the settings from the Text To Columns dialog box (and making no changes to your workbook).

Sub ClearTextToColumns()
    On Error Resume Next
    If IsEmpty(Range("A1")) Then Range("A1") = "XYZZY"
    Range("A1").TextToColumns Destination:=Range("A1"), _
        DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, _
        Tab:=False, _
        Semicolon:=False, _
        Comma:=False, _
        Space:=False, _
        Other:=False, _
    If Range("A1") = "XYZZY" Then Range("A1") = ""
    If Err.Number <> 0 Then MsgBox Err.Description
End Sub

This macro assumes that a worksheet is active, and it's not protected. Note that the contents of cell A1 will not be modified because no operations are specified for the TextToColumns method.

If cell A1 is empty, the code inserts a temporary string (because the TextToColumns method will fail if the cell is empty). Before ending, the procedure deletes the temporary string.

Making An Exact Copy Of A Range Of Formulas, Take 2

Category: General / Formulas | [Item URL]

When you copy a range of formulas and paste them to a new location, Excel adjusts the cell references automatically. Most of the time, this is exactly what you want. Consider this simple formula:


If you copy this formula and paste it to the next column, the references are adjusted and the pasted formula is:


Making an exact copy of a single formula is easy: Press F2, highlight the formula, and press Ctrl+C to copy it as text. Then paste it to another cell. In some situations, however, you might need to make an exact copy of a range of formulas. In an older tip, I described a rather complicated way to do this. See Making An Exact Copy Of A Range Of Formulas.

Matthew D. Healy saw that tip and shared another method, which uses Notepad. Here's how it works:

  1. Put Excel in formula view mode. The easiest way to do this is to press Ctrl+` (that character is a "backwards apostrophe," and is usually on the same key that has the ~ (tilde).
  2. Select the range to copy.
  3. Press Ctrl+C
  4. Start Windows Notepad
  5. Press Ctrl+V to past the copied data into Notepad
  6. In Notepad, press Ctrl+A followed by Ctrl+C to copy the text
  7. Activate Excel and activate the upper left cell where you want to paste the formulas. And, make sure that the sheet you are copying to is in formula view mode.
  8. Press Ctrl+V to paste.
  9. Press Ctrl+` to toggle out of formula view mode.

Note: If the paste operation back to Excel doesn't work correctly, chances are that you've used Excel's Text-to-Columns feature recently, and Excel is trying to be helpful by remembering how you last parsed your data. You need to fire up the Convert Text to Columns Wizard. Choose the Delimited option and click Next. Clear all of the Delimiter option checkmarks except Tab.

Create A Drop-Down List Of Possible Input Values

Category: General | [Item URL]

If you're creating a worksheet that will require user input and you want to minimize data entry errors, use Excel's data validation feature to add a drop-down list. The best part about it is that you don't have to write any macros.

Data validation is an excellent way to ensure that a cell entry is of the proper data type (text, number, or date) and within the proper numeric range. The drop-down list produced with the feature appears when a user clicks the cell.

Here's how to create a drop-down list:

  1. Type the list of valid entries in a single column. If you like, you can hide this column (select Format, Column, Hide).
  2. Select the cell or cells that will display the list of entries.
  3. Choose Data, Validation, and select the Settings tab.
  4. From the Allow drop-down list, select List.
  5. In the Source box, enter a range address or a reference to the items that you entered in step 1.
  6. Make sure the 'In-cell dropdown' box is selected.
  7. Click OK.

If your list is short, you can skip step 1 and type the list entries directly in the Source box in step 5, separating items with a comma.

The Data Validation dialog box has two other tabs. Click Input Message to add a prompt that will appear when a user selects a cell. Click Error Alert to specify a custom error message if the user's entry is invalid.

The handy data validation feature suffers from one serious flaw. If you paste an entry into a cell that uses data validation, the validation isn't performed. And if you select that cell again, the drop-down list no longer appears. Fortunately, you can circumvent this problem by protecting the worksheet: Select Tools, Protection, Protect Sheet.

Using Custom Number Formats

Category: General | [Item URL]

A companion file is available: Click here to download

One of the most useful (and underutilized) features in Excel is the ability to create custom number formats. Although Excel offers a wide variety of standard number formats, it's often advantageous to develop custom formats. For example, if you use large values, you can scale the display of those values so they appear "in thousands." You can make 123,456,789 appear as 123,457 by applying this format:


A number format consists of three parts: code for negative, code for zero, and code for positive values. The code for each part is separated with a semicolon. If you would like a cell to appear empty, use this format (which is three semicolons and nothing else):


To apply a custom number format:

  1. Select the cell or range that you want to format
  2. Choose the Format Cells command (or press Ctrl+1)
  3. Click the Number tab on the Format Cells dialog box
  4. Click the Custom category
  5. Enter the number format code into the edit box labeled Type.

Rather than provide a complete tutorial on custom number formats, I refer you to Excel's help. You'll find a comprehensive reference for the formatting codes.

NOTE: It's important to understand that a number format affects only the way in which the number appears. A number format does not change the underlying value in the cell.

Navigating Excel’s Sheets

Category: General | [Item URL]

Every Excel user knows that you can activate a different sheet in a workbook by clicking its sheet tab. Most users also know that you can press Ctrl-Page Up to activate the previous sheet, and Ctrl-Page Down to activate the next sheet.

But if your workbook contains many sheets, and not all of the sheets' tabs fit on the screen, you may find it tedious to scroll through the tabs or use the keyboard to activate a distant sheet.

Here's a little-known trick that will allow you to bypass repeated scrolling or typing: Display a pop-up list of sheet names by right-clicking one of the arrows to the left of the sheet tabs that are located at the bottom of the worksheet window (see the figure below). Select a sheet from the list, and you're there in a flash.

Override Excel’s Text Import Wizard

Category: General | [Item URL]

If you import text files into Excel, you've undoubtedly encountered the Text Import Wizard, which guides you through parsing the text.

If the files you import are always parsed correctly, you may prefer to bypass this wizard and accept the defaults. To do so:

  1. Select File, Open
  2. Locate the file to be imported
  3. Hold down Shift while you click Open

Note: When you import a file with a .csv extension, the Text Import Wizard won't kick in. To override this default, you need to change the file's extension from .csv to .txt.

Sharing Autocorrect Shortcuts

Category: General / General VBA | [Item URL]

Q. I've set up approximately 200 Excel AutoCorrect shortcuts that represent various products and services offered by my company. What's the best way to transfer these shortcuts to other systems so that my coworkers can use them?

AutoCorrect, which debuted in Excel 95, can correct common spelling errors on the fly. As many users of Excel have discovered, you can also assign "shorthand shortcuts" that expand to a longer sequence of characters. For example, you may define "awc" as an AutoCorrect shortcut for "Amalgamated Widget Corporation of America." When you type awc into a cell, Excel will then replace it with the associated text string. You define and manage your shortcuts by using the Tools, AutoCorrect command.

Excel and all other Microsoft Office applications store AutoCorrect shortcuts in a single *.acl file in your Windows folder (the exact file name will vary). So changes you make from Excel, say, will be available in Word. However, there's no tool for manually editing the binary *.acl file and moving such a file from one system to another will invalidate your existing entries. The solution is to use a VBA macro to create the shortcuts.

Start Excel with a new workbook with one sheet, and then enter your shortcuts and their associated text into columns A and B, respectively (as in the figure below). Enter as many as you like, beginning in row 1, and don't include any blank rows between the entries. Save and name this worksheet.

Select Tools, Macro, Macros to display the Macros dialog box. Type CreateShortcuts in the Macro Name field and click Create. Then enter the following macro into the VBA module, and press Alt-F11 to return to Excel.

Sub CreateShortcuts()
  ItemCount = Application.CountA(Range("Sheet1!A:A"))
  For Row = 1 To ItemCount
    ShortText = Cells(Row, 1)
    LongText = Cells(Row, 2)
    Application.AutoCorrect.AddReplacement ShortText, LongText
  Next Row
End Sub

Save the workbook and distribute it to your coworkers. To add the AutoCorrect shortcuts, open the workbook, select Tool, Macro, Macros, and then execute the CreateShortcuts macro. Be aware that existing shortcuts with the same name will be overwritten without warning.

Making A Worksheet Very Hidden

Category: General | [Item URL]

You can use Excel's Format, Sheet, Hide to hide an entire worksheet. But unless you password-protect the workbook structure, anyone can select Format, Sheet, Unhide to see the hidden sheet.

If you use Excel 97 or later, here's another option:

  1. Press Alt-F11 to display the Visual Basic Editor
  2. in the Project window, double-click Microsoft Excel Objects and select the sheet to hide.
  3. Press F4 to display the Property box
  4. Set the sheet's Visible property to xlSheetVeryHidden.

To unhide the sheet, repeat these step, but set the Visible property to xlSheetVisible.

Page 1 of 2 pages
[Next page]

Search for Tips

All Tips

Browse Tips by Category

Tip Books

Needs tips? Here are two books, with nothing but tips:

Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks

Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers

© Copyright 2016, J-Walk & Associates, Inc.
Privacy Policy