Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

UserForms

My macro needs to get just a few pieces of information from the user , and a UserForm seems like overkill. Are there any alternatives?

Yes, check out VBA's MsgBox function and its InputBox function. Alternatively, you might want to use Excel's InputBox method.

I have 12 CommandButtons on a UserForm. How can I assign a single macro to be executed when any of the buttons is clicked?

There is no easy way to do this because each CommandButton has its own Click event procedure. One solution is to call another procedure from each of the CommandButton_Click procedures. Another solution is to use a class module to create a new class. This technique is described in Chapter 15.

How can I display a chart in a UserForm?

There is no direct way to do this. One solution is to write a macro that saves the chart to a GIF file and then loads the GIF file into an Image control on the UserForm.

How can I remove the "X" from the title bar of my UserForm? I don't want the user to click that button to close the form.

Removing the Close button on a UserForm's title bar requires some complex API functions. A simpler approach is to intercept all attempts to close the UserForm by using a UserForm_QueryClose event procedure in the code module for the UserForm. The following example does not allow the user to close the form by clicking the Close button:

 Private Sub UserForm_QueryClose _   (Cancel As Integer, CloseMode As Integer)     If CloseMode = vbFormControlMenu Then         MsgBox "You can't close the form like that."         Cancel = True     End If End Sub 

I created a UserForm with controls that are linked to cells on the worksheet with the ControlSource property. Is this the best way to do this?

In some cases, using links to worksheet cells can slow your application because the worksheet is recalculated every time a control changes the cell . In addition, if your UserForm has a Cancel button, the cells might have already been changed when the user clicks Cancel.

Can I create a control array for a UserForm? It's possible with Visual Basic, but I can't figure out how to do it with Excel VBA.

You can't create a control array, but you can create an array of Control objects. The following code creates an array consisting of all CommandButton controls:

 Private Sub UserForm_Initialize()     Dim Buttons() As CommandButton     Cnt = 0     For Each Ctl In UserForm1.Controls         If TypeName(Ctl) = "CommandButton" Then             Cnt = Cnt + 1             ReDim Preserve Buttons(1 To Cnt)             Set Buttons(Cnt) = Ctl         End If     Next Ctl End Sub 

Is there any difference between hiding a UserForm and unloading a UserForm?

Yes, the Hide method keeps the UserForm in memory but makes it invisible. The Unload statement unloads the UserForm, beginning the "termination" process (invoking the Terminate event for the UserForm) and removing the UserForm from memory.

How can I make my UserForm stay open while I do other things?

By default, each UserForm is modal, which means that it must be dismissed before you can do anything else. However, you can make a UserForm modeless by using vbModeless as the argument for the Show method. Here's an example:

 UserForm1.Show vbModeless 

Excel 97 gives me a compile error when I write UserForm1.Show vbModeless. How can I make the form modeless in Excel 2000 and later while allowing it to remain modal in Excel 97?

Test for the version of Excel that the user is running and then execute a separate procedure if the version is Excel 2000 or later. The following code demonstrates how:

 Sub ShowUserForm()     If Val(Application.Version) >= 9 Then       ShowModelessForm     Else       UserForm1.Show     End If End Sub Sub ShowModelessForm()     Dim frm As Object     Set frm = UserForm1     frm.Show 0 ' vbModeless End Sub 

Because the ShowModelessForm procedure is not executed in Excel 97, it will not cause a compile error.

I need to display a progress indicator like those you see when you're installing software while a lengthy process is being executed. How can I do this?

You can do this with a UserForm. Chapter 15 describes several different techniques, including one in which the code gradually stretches a shape inside a frame while the lengthy macro is running.

How can I use Excel's shapes on my UserForm?

You can't use the shapes directly with a UserForm, but you can do so indirectly. Start by adding a shape to a worksheet. Then select the shape and choose Home Clipboard Copy. Activate your UserForm and insert an Image object. Press F4 to display the Properties window. Select the Picture property and press Ctrl+V to paste the Clipboard contents to the Image control. You might also need to set the AutoSize property to True .

How can I generate a list of files and directories into my UserForm so the user can select a file from the list?

There's no need to do that. Use VBA's GetOpenFilename method. This method displays an Open dialog box in which the user can select a drive, directory, and file.

I need to concatenate strings and display them in a ListBox control. But when I do so, they aren't aligned properly. How can I get them to display equal spacing between strings?

You can use a monospaced font such as Courier New for the ListBox. A better approach, however, is to set up your ListBox to use two columns . (See Chapter 14 for details.)

Is there an easy way to fill a ListBox or ComboBox control with items?

Yes, you can use an array. The statement that follows adds three items to ListBox1 .

 ListBox1.List = Array("Jan", "Feb", "Mar") 

Can I display a built-in Excel dialog box from VBA?

Many of Excel's dialog boxes can be displayed by using the Application.Dialogs method. For example, the following instruction displays the dialog box that enables you to format numbers in cells:

 Application.Dialogs(xlDialogFormatNumber).Show 

Use the Object Browser to display a list of the constants for the built-in dialog boxes. Press F2 from the VBE, select the Excel library, and search for xlDialog. You'll probably need to use some trial and error to locate the constant that corresponds to the dialog box that you want to display.

In Excel 2007, you can execute Ribbon commands (including those that display a dialog box) by using the ExecuteMso method along with the control name . The statement that follows, for example, displays the dialog box that enables you to format numbers in a cell:

 Application.CommandBars.ExecuteMso("NumberFormatsDialog") 

See Chapter 22 for more information.

I tried the technique described in the preceding question and received an error message. Why is that?

The Dialogs method (and the ExecuteMso method) will fail if the context isn't appropriate. For example, if you attempt to display the Chart Type dialog box ( xlDialogChartType ) when a chart is not activated, you'll get an error message.

Every time I create a UserForm, I go through the steps of adding an OK button and a Cancel button. Is there a way to get these controls to appear automatically?

Yes. Set up a UserForm with the controls that you use most often. Then choose File Export File to save the UserForm. When you want to add a new form to another project, choose File Import File.

Can I create a UserForm without a title bar?

Yes, but it requires some complex API functions.

When I click a button on my UserForm, nothing happens. What am I doing wrong?

Controls added to a UserForm do nothing unless you write event handler procedures for them. These procedures must be located in the code module for the UserForm, and they must have the correct name.

Can I create a UserForm whose size is always the same, regardless of the video display resolution?

You can, but it's probably not worth the effort. You can write code to determine the video resolution and then use the Zoom property of a UserForm to change its size. The normal way to deal with this matter is simply to design your UserForm for the lowest resolution that will be used - probably a 1024 — 768 display.

Can I create a UserForm box that lets the user select a range in a worksheet by pointing?

Yes. Use the RefEdit control for this. See Chapter 14 for an example.

Can I change the startup position of a UserForm?

Yes, you can set the UserForm's Left and Top properties. But for these to be effective, you need to set the UserForm's StartUpPosition property to .

Can I make a UserForm that's resizable by the user?

Yes. See Chapter 15 for an example.