Developer FAQ - UserForms
Note: This document was written for Excel 97 - 2000.
I need to get just a few pieces of information 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 subroutine from each of the CommandButton_Click subroutines. Another solution is to use a class module to create a new class. Follow this link for an example.
Is there any way to display a chart in a UserForm?
There is not direct way to do this. One solution is to write that saves the chart to a GIF file, and then loads the GIF file into an Image control. Follow this link for an example.
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.
You can't remove the Close button on a UserForm's title bar. However, you can intercept all attempts to close the UserForm by using a UserForm_QueryClose event procedure in the code module for the UserForm. The example below 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've created a UserForm, and the controls are linked to cells on the worksheet. Is this the best way to do this?
In general, you should avoid using links to worksheet cells unless you absolutely must. Doing so can slow your application down, because the worksheet is recalculated every time a control changes the cell.
Is there any way to 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.
You can't create a control array, but you can create an array of Control objects. The code below 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.
How can I make my UserForm stay open while I do other things?
Excel 97 UserForms are modal -- which means that the form must be dismissed before you can do anything else. Excel 2000, however, supports modeless UserForms.
I need to display a progress indicator (like those used during software installation) while a lengthy process is being executed. How can I do this?
You can do this with a UserForm. Follow this link for an example.
How can I get 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 statement. This displays a "file open" dialog box in which the user can select a drive, directory, and file.
I have several 1-2-3 for Windows files and Quattro Pro for Windows files that contain custom dialog boxes. Is there a utility to convert these to Excel dialog boxes?
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.
Is it possible to display a built-in Excel dialog box from VBA?
Most (but not all) of Excel's dialog boxes can be displayed by using the Application.Dialogs method. For example, the following statement displays the dialog box that lets you format numbers in cells:
Use the Object Browser to display a list of the constants for the built-in dialog boxes. Press F2 from the VBE, then select the Excel library and then the Constants object. The Method/Properties list will display the constants for the built-in dialog boxes (they all begin with xlDialog).
I tried the technique in the preceding question and received an error message. Why is that?
The Dialogs 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, create a UserForm set up with the controls you use most often. Then select File - Export File to save the UserForm. When you want to add a new form to another project, select File - Import File.
Is it possible to create a UserForm without a title bar?
No. The closest you can get is to make the dialog box's caption blank by setting the Caption property to an empty string.
I recorded some VBA code to print to a file. However, there seems to be no way to supply the filename in my code. No matter what I try, I keep getting the prompt to supply a filename.
This was a common problem that was corrected in Excel 2000. In Excel 2000, you can provide a PrToFileName argument for the PrintOut method. Here's an example:
ActiveSheet.PrintOut _ PrintToFile:=True, PrToFileName:="test.prn"
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.
I wrote a subroutine named Workbook_Open, but it doesn's get executed when the workbook is opened.
The most likely cause is that your subroutine is located in a normal VBA module. Workbook event procedures must be located in the code module for the ThisWorkbook object.
Can I create a custom dialog box that displays in the same size, 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 make use of the Zoom property of a UserForm to change its size. The normal way to deal with this sort of thing is to simply design your UserForm for a 640x480 display.
Is it possible to create a UserForm box that lets the user select a range in a worksheet by pointing?
Yes. Use the RefEdit control for this.
Is there a way to change the start-up position of a UserForm?
Yes, you can set the UserForm's Left and Top properties. But in order for these to be effective you need to set the StartUpPosition property to 0.
Can I add an Excel 5/95 dialogsheet to my workbook?
Right-click any sheet tab in a workbook and select Insert from the shortcut menu. In the Insert dialog box, select MS Excel 5.0 Dialog.
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