Excel 2010 Bible

xxxv

Contents

Chapter 42: Using UserForm Controls in a Worksheet . . . . . . . . . . . . . . 859

Why Use Controls on a Worksheet? ..................................................................................859

Using Controls ..................................................................................................................861

Adding a control .....................................................................................................862

About design mode .................................................................................................862

Adjusting properties ................................................................................................862

Common properties ................................................................................................863

Linking controls to cells ..........................................................................................864

Creating macros for controls ...................................................................................864

Reviewing the Available ActiveX Controls .........................................................................866

CheckBox ................................................................................................................866

ComboBox ..............................................................................................................867

CommandButton .....................................................................................................868

Image ......................................................................................................................868

Label .......................................................................................................................86 8

ListBox ....................................................................................................................868

OptionButton ..........................................................................................................869

ScrollBar..................................................................................................................869

SpinButton ..............................................................................................................870

TextBox ...................................................................................................................871

ToggleButton ...........................................................................................................872

Chapter 43: Working with Excel Events . . . . . . . . . . . . . . . . . . . . . . . . . . 873

Understanding Events .......................................................................................................873

Entering Event-Handler VBA Code ...................................................................................874

Using Workbook-Level Events ..........................................................................................875

Using the Open event ..............................................................................................876

Using the SheetActivate event ..................................................................................878

Using the NewSheet event .......................................................................................878

Using the BeforeSave event ......................................................................................878

Using the BeforeClose event ....................................................................................879

Working with Worksheet Events ......................................................................................879

Using the Change event ...........................................................................................880

Monitoring a specific range for changes ...................................................................881

Using the SelectionChange event .............................................................................882

Using the BeforeRightClick event ............................................................................883

Using Non-Object Events ..................................................................................................883

Using the OnTime event .........................................................................................883

Using the OnKey event............................................................................................884

Chapter 44: VBA Examples  . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 887

Working with Ranges ........................................................................................................887

Copying a range ......................................................................................................888

Copying a variable-size range ..................................................................................889

Selecting to the end of a row or column ..................................................................890

02_474877-ftoc.indd xxxv02_474877-ftoc.indd xxxv 3/29/10 11:34 PM3/29/10 11:34 PM