Excel VBA Programming For Dummies

ListBoxes are useful controls, but working with them can be a bit tricky. Before displaying a dialog box that uses a ListBox, fill the ListBox with items. Then when the dialog box is closed, you need to determine which item(s) the user selected.

Sub UserForm_Initialize()

' Fill the list box

With ListBox1

.AddItem "January"

.AddItem "February"

.AddItem "March"

.AddItem "April"

.AddItem "May"

.AddItem "June"

.AddItem "July"

.AddItem "August"

.AddItem "September"

.AddItem "October"

.AddItem "November"

.AddItem "December"

End With

' Select the first list item

ListBox1.ListIndex = 0

End Sub

Using a UserForm as a progress indicator

If you have a macro that takes a long time to run, you might want to display a progress meter so people won’t think Excel has crashed. You can use a UserForm to 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.

The Excel GetSaveAsFilename method works just like the GetOpenFilename method, but it displays the Excel Save As dialog box rather than its Open dialog box. The GetSaveAsFilename method gets a path and filename from the user but doesn’t do anything with that information. It’s up to you to write code that actually saves the file.

The syntax for this method follows:

object.GetSaveAsFilename ([InitialFilename], [FileFilter], [FilterIndex], [Title], [ButtonText])

The GetSaveAsFilename method takes T

Every UserForm object has a Code module that holds the VBA code (the event-handler procedures) executed when the user works with the dialog box. To view the Code module, press F7. The Code window is empty until you add some procedures. Press Shift+F7 to return to the dialog box.

The following statement uses the VBA TypeName function to display the type of object that is currently selected:

MsgBox TypeName(Selection)

If a Range object is selected, the MsgBox displays Range. If your macro works only with ranges, you can use an If statement to ensure that a range is selected. This example displays a message and exits the procedure if the current selection is not a Range object:

Sub CheckSelection()

If TypeName(Selection) <> "Range" Then

The Accelerator property determines which letter in the caption is underlined.

ActiveWindow.DisplayHeadings = Not _
ActiveWindow.DisplayHeadings

Set ConstantCells = Selection _
.SpecialCells(xlConstants)

To create a watch expression, choose Debug⇒Add Watch to display the Add Watch dialog box. See Figure 13-6.