Using Controls On Worksheets
A companion file is available: Click here to downloadMost developers know that it's possible to insert dialog box controls (such as buttons and checkboxes) directly on a worksheet. This topic, however, is the source of a great deal of confusion. This tip attempts to clarify some of the issues related to using controls on worksheets.
Two types of controls
First, it's important to understand that Excel supports two general types of controls:
- ActiveX Controls
- Excel Controls
These two types of controls are described in the table below.
|ActiveX Controls||Excel Controls|
|Excel versions||97, 2000||5, 95, 97, 2000|
|Which toolbar?||Control Toolbox||Forms|
|Controls available||CheckBox, TextBox, CommandButton, OptionButton, ListBox, ComboBox, ToggleButton, SpinButton, ScrollBar, Label, Image||Label, GroupBox, Button, CheckBox, OptionButton, ListBox, ComboBox, ScrollBar, Spinner|
|Macro code storage||In the code module for the Sheet||In any standard VBA module|
|Macro name||Corresponds to the control name (e.g., CommandButton1_Click)||Any name you specify.|
|Correspond to...||UserForm controls||Dialog Sheet controls|
|Customization||Extensive, using the Properties box||Minimal|
|Respond to events||Yes||Click or Change events only|
Using ActiveX controls
To insert an ActiveX control on a worksheet, make sure the Control Toolbox toolbar is displayed. Then click the desired control, and drag in the sheet to create the control. After adding a control, Excel is in "design mode." To make any changes to the control, Excel must be in design mode. To test the control, Excel must not be in design mode. Use the Design Mode button on the Control Toolbox toolbar to toggle design mode.
To customize the control, right-click it and select Properties. This displays the Properties window. Each control has its own set of properties.
Some ActiveX controls can be connected to a cell (i.e., the LinkedCell property). Other controls can receive information from a range. For example, a ListBox control can get its list data from the ListFillRange property. Consult the online help for complete details regarding the properties for each control.
Each ActiveX control also triggers events. For example, a CommandButton control generates a Click event. You can write an event-handler Sub procedure to respond to the events. The procedure below, for example, displays a message box when a CommandButton (named CommandButton1) is clicked:
Private Sub CommandButton1_Click() MsgBox "CommandButton1 was clicked." End Sub
Event-handler procedures must be located in the code module for the sheet on which the control is placed. It's important to understand that you cannot assign an arbitrary macro to an ActiveX control. To access the correct code module, right-click the control and choose View Code. The code module displays two drop-down controls at the top. Use these controls to determine the events supported for your control.
Using Excel controls
To insert an Excel control on a worksheet, make sure the Forms toolbar is displayed. Then click the desired control and drag in the sheet to create the control.
Right-click the control to access the Format dialog box. Any customizations
to the control are made using this dialog box. Formatting options are limited.
To assign a macro to the control, right-click it and select Assign Macro. Excel
controls can execute any macros.
Navigating Excel’s Sheets
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
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:
- Select File, Open
- Locate the file to be imported
- 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.
Copy Page Setup Settings To Other Sheets
Each Excel sheet has its own print setup options (orientation, margins, headers and footers, and so on). These options are specified in the Page Setup dialog box, which you access using the File, Page Setup command.
When you add a new sheet to a workbook, it contains the default page setup setting. Here's an easy way to transfer the settings from one worksheet to additional worksheets:
- Activate the sheet that contains the desired setup info. This is the "source" sheet.
- Select the "target" sheets. Press Ctrl and click the sheet tabs of the sheets you want to update with the settings from the source sheet.
- Select File, Page Setup and click OK.
The Page Setup settings of the source sheet will be transferred to all of the target sheets.
Sharing Autocorrect Shortcuts
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
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:
- Press Alt-F11 to display the Visual Basic Editor
- in the Project window, double-click Microsoft Excel Objects and select the sheet to hide.
- Press F4 to display the Property box
- Set the sheet's Visible property to xlSheetVeryHidden.
To unhide the sheet, repeat these step, but set the Visible property to xlSheetVisible.
Importing A Text File Into A Worksheet
Excel doesn't provide any obvious way to insert the contents of a text file into a worksheet. Until Excel 2000, all versions of Excel required you to open the text file separately, then copy and paste the data to your worksheet.
Excel 2000 (and later) makes the process much easier. You might expect to see this feature on the File menu, but instead you select Data, Get External Data, Import Text File. This command calls up a dialog box in which you select a text file, whereupon the Text Import Wizard lets you specify how Excel should import the data. Finally, in the Import Data dialog box, you indicate the desired worksheet destination.
Excel imports the text file as a database query, which
you can update if the data in the text file changes (select Data, Refresh
Data to do so). To import the file as static data, click Properties in the
Import Data dialog, and remove the check mark from the "Save query definition"
Protecting Cells, Sheets, Workbooks, And Files
Excel offers many ways to protect your data. But understanding the differences between the methods and the limitations of each of them can be daunting. Here's a quick review of four protection options in Excel 97 and later, plus explanations of how to use them.
Note: These protection features aren't foolproof. Password-cracking utilities exist, and anyone who wants to defeat your protection badly enough probably can.
Every cell has two key properties: locked and hidden. A locked cell can't be changed, and the contents of a hidden cell don't appear in the formula bar when the cell is selected. By default, every cell is locked and not hidden. Locked and hidden attributes have no effect unless the worksheet is protected.
To change these attributes, select the appropriate cell or a range and then choose Format, Cells. In the Format Cells dialog box, click the Protection tab and select Locked or Hidden. Unlock cells that accept user input, and lock formula cells and other cells that should stay unchanged (such as titles). To prevent others from seeing your formulas, lock and hide the formula cells--the results of the formulas will be visible, but the formulas will not.
To protect a sheet, choose Tools, Protection, Protect Sheet to bring up the Protect Sheet dialog, and make sure Contents is checked. You can enter a password to prevent others from unprotecting the sheet. Locked cells in a protected sheet cannot be edited, and other worksheet changes are disabled. For example, no one can insert rows or columns, change column width, or create embedded charts.
Note: Excel 2002 offers additional options regarding what the user can and cannot do when a sheet is protected. For example, you can protect a sheet and still allow sorting and autofiltering.
Workbook window protection
The Tools, Protection, Protect Workbook command enables you to protect a workbook so no one can add or delete sheets or resize or move the workbook window.
Workbook file protection
Here's how to save a workbook in such a way that you must supply a password to open or modify it:
- In Excel 97, select File, Save As to display the Save As dialog box; then click Options in that box to call up the Save Options dialog.
- In Excel 2000 and later, select Tools, General Options when the Save As dialog box appears. If you enter a password in the "Password to open" field, the user must provide the correct password to open the file. If you enter a password in the "Password to modify" field, the user must give the right password to make changes to the file. Without the correct password, the file opens in read-only mode.
Note: Excel 2002 and later also lets you specify the type of encryption, if any, that will be used. Access this feature by clicking the Advanced button in the Save Options dialog box.
VBA project protection
If your workbook contains VBA macros, you can use these to prohibit others from viewing or modifying them. Press Alt-F11 to activate the VB Editor window, and select your project from the Project window. Choose Tools, VBA Project Properties. Click the Protection tab, place a check mark next to "Lock project for viewing," and enter the password twice. Click OK and save your workbook.
Changing The Number Of Sheets In A New Workbook
By default, each new Excel workbook begins life with three worksheets. You can, of course, add more sheets to the workbook or delete sheets you don't need. The unused sheets don't occupy additional memory or increase file size, but I generally don't like them in my workbooks.
A better approach is to change the default. Select Tools, Options and click General in the Options dialog box. Then change the setting for "Sheets in new workbook". Now all new workbooks will have the number of sheets you specified.
I recommend that you change this setting to 1.
Restrict Cursor Movement To Unprotected Cells
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.
Search for Tips
Browse Tips by Category
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