The Before DoubleClick event

You can set up a VBA procedure to be executed when the user double-clicks a cell. In the following example which is stored in the Code window for a Sheet object , double-clicking a cell makes the cell bold if it's not bold or not bold if it is bold Private Sub Worksheet_BeforeDoubleClick _ ByVal Target As Excel.Range, Cancel As Boolean Target.Font.Bold Not Target.Font.Bold Cancel True End Sub The Worksheet_BeforeDoubleClick procedure has two arguments Target and Cancel. Target represents the...

The Before RightClick event

The BeforeRightClick event is similar to the BeforeDoubleClick event, except that it consists of right-clicking a cell. The following procedure checks to see whether the cell that was right-clicked contains a numeric value. If so, the code displays the Format Number dialog box and sets the Cancel argument to True avoiding the normal shortcut menu display . If the cell does not contain a numeric value, nothing special happens the shortcut menu is displayed as usual. Private Sub...

Wrapping Your Mind around Collections

Office Vba Object Model

Collections are another key concept in VBA programming. A collection is a group of objects of the same type. And to add to the confusion, a collection is itself an object. Here are a few examples of commonly used collections i Workbooks A collection of all currently open Workbook objects i Worksheets A collection of all Worksheet objects contained in a particular Workbook object i Charts A collection of all Chart objects chart sheets contained in a particular Workbook object i Sheets A...

Using a User Form as a progress indicator

Userform Visual Basic Excel

One of the most common Excel programming questions I hear is How can I make a UserForm display the progress of a lengthy macro Use Excel's custom dialog box to easily create an attractive progress indicator, as shown in Figure 18-9. Such a use of dialog boxes does, however, require a few tricks which I'm about to show you. This UserForm functions as a progress indicator for a lengthy macro. This UserForm functions as a progress indicator for a lengthy macro. Creating the progress indicator...

Custom Dialog Box Alternatives

Saving time by using any of several alternatives to custom dialog boxes Using the InputBox and MsgBox functions to get information from the user Getting a filename and path from the user Writing VBA code to display any of the Excel built-in dialog boxes Xou can't use Excel very long without being exposed to dialog boxes. They seem to pop up almost every time you select a command. Excel like most Windows programs uses dialog boxes to obtain information, clarify commands, and display messages....

Creating a tabbed dialog box

Tabbed dialog boxes are useful because they let you present information in small, organized chunks. The Excel Options dialog box which is displayed when you choose ToolsOOptions is a good example. This dialog box uses a whopping 13 tabs to add some organization to an overwhelming number of options. Creating your own tabbed dialog boxes is relatively easy, thanks to the MultiPage control. Figure 18-11 shows a custom dialog box that uses a MultiPage control with three pages, or tabs. When the...