Creating A Color Picker Dialog Box

Category: UserForms | [Item URL]

A companion file is available: Click here to download

This tip demonstrates a technique to display a dialog box that allows the user to select a color.

How it works

The example file contains the following:

• A UserForm (UserForm1) that contains a dialog box with 56 buttons.
• A Class Module (Class1) that defines a ColorButton Class.
• A VBA module (Module1) that contains a function (GetAColor).
• Two example subroutines that demonstrate the GetAColor function.

The GetAColor function sets up the dialog box and display it (see the figure below). The function returns the color value of the selected button. If the user clicks Cancel, the GetAColor function returns False. As the user moves the mouse pointer over the color buttons, the Color Sample image displays the color.

Using the function

To use the GetAColor function in your own workbooks, export UserForm1, Module1, and Class1, and then import them into your workbook. Write your own subroutine that calls the GetAColor function.

Displaying A Chart In A Userform

Category: UserForms | [Item URL]

A companion file is available: Click here to download

One of my favorite Excel tricks is to display a chart in a UserForm. You might think that this would be easy: Just add a linked picture of the chart to the UserForm. Unfortunately, that's not possible.

This tip describes a workaround that involves saving the chart as a GIF file, and then displaying the GIF in an Image control. This ensures that the UserForm always displays the current version of the chart.

You might notice a slight delay as the chart is saved and then retrieved. On a fast system, however, this delay is barely noticeable.

How it works

To set this up:

1. Create your chart or charts as usual.
2. Insert a UserForm and then add an Image control.
3. Write VBA code to save the chart as a GIF file, and then set the Image control's Picture property to the GIF file. You need to use the LoadPicture function to do this.
4. Add other bells and whistles as desired. For example, the demo file displays multiple charts, and the user can cycle through them.

Saving a chart as a GIF file

The code below demonstrates how to create a GIF file (named temp.gif) from a chart (the first chart object on Sheet1).

    Set CurrentChart = Sheets("Sheet1").ChartObjects(1).Chart
    Fname = ThisWorkbook.Path & "\temp.gif"
    CurrentChart.Export FileName:=Fname, FilterName:="GIF"

Changing the Image control's Picture Property

If the Image control on the UserForm is named Image1, the statement below loads the image (represented by the Fname variable) into the Image control.

        Image1.Picture = LoadPicture(Fname)

Adding A Hyperlink To A Userform

Category: UserForms | [Item URL]

A companion file is available: Click here to download

You probably know that the Insert Hyperlink command (or Ctrl+K) lets you insert a hyperlink into any cell. But if you've tried to add a hyperlink to a UserForm, you may have come up empty-handed.

There is no direct way to add a hyperlink to a UserForm, but you can simulate one by using the techniques described here. The figure below shows an example.

(Yes, this is a REALLY old screen shot!)

1. Add a Label object and enter some text for its Caption
2. Make the Label blue and underlined so it resembles a typical hyperlink. You might also want to set its font to Courier New.
3. None of the standard mouse pointers resembles a pointing hand, so set the Label's MousePointer property to: 99 - fmMousePointerCustom
4. Specify the cursor file for the Label's MouseIcon image. If you don't have a cursor file that resembles a pointing hand, click here to download a file named hand.cur.
5. Double-click the Label and enter an event-handler subroutine for its Click event. The FollowHyperlink method is what makes it work. Here's an example of such a subroutine:

Private Sub Label1_Click()
    Link = "http://www.whitehouse.gov"
    On Error GoTo NoCanDo
    ActiveWorkbook.FollowHyperlink Address:=Link, NewWindow:=True
    Unload Me
    Exit Sub
NoCanDo:
    MsgBox "Cannot open " & Link
End Sub

To create a "mail to" hyperlink, use a statement like this:

    Link = "mailto:president@whitehouse.gov"

Creating A Userform Programmatically

Category: UserForms | [Item URL]

A companion file is available: Click here to download

Many VBA programmers don't realize it, but it's possible to create a UserForm using VBA. In addition, you can write code to add controls to the UserForm, and even create event-handler procedures in the UserForm's code module.

This is possible because of the Visual Basic Integrated Development Environment (the VBIDE). The VBIDE is described in the online help, but it's very sketchy and provides few examples. The example in this document may help you understand how the VBIDE works.

The GetOption Function

This document describes a function named GetOption, which takes three arguments:

• OpArray: An string array that holds the items to be displayed in the form as OptionButtons
• Default: An integer that specified the default OptionButton that is selected when the UserForm is displayed. If zero, none of the OptionsButtons is displayed.
• Title: Text to display in the title bar of the UserForm

When the function is called it performs the following tasks:

1. It creates a new UserForm.
2. It adds OptionButton controls to the UserForm, the number of which corresponds to the number of elements in the OpArray argument.
3. It adds two CommandButton controls to the UserForm (OK and Cancel).
4. It creates two subroutines to handle the Click events for the CommandButtons.
5. Adjusts the size of the UserForm to accommodate the OptionButtons.
6. It displays the UserForm and waits for the user's response.
7. It returns the index number of the selected item to the calling procedure -- or it returns False if the UserForm was canceled.
8. It deletes the UserForm.

The GetOption function is very useful for soliciting information from a user, and is often an excellent alternative to MsgBox or InputBox -- and it's certainly easier than creating a custom UserForm.

Contrary to what you might expect, this process is very fast -- virtually instantaneous on my system.

Using the GetOption function

The function is completely self-contained in a module. Consequently, it can be added to any existing project by importing the *.BAS file or by copying and pasting the code.

The example below is a simple subroutine that demonstrates the use of GetOption. It creates a 12-item array (Ops) that consists of the month names. It then calls the GetOption function, passing the following arguments: The 12-item array, a literal 1 (the first item is the default), and a literal string ("Select a month").

Sub DemoGetOption()
    Dim Ops(1 To 12) As String
'   Create an array of month names
    For i = 1 To 12
        Ops(i) = Format(DateSerial(1, i, 1), "mmmm")
    Next i
    UserChoice = GetOption(Ops, 1, "Select a month")
    MsgBox UserChoice
End Sub

The figure below shows the UserForm as it is displayed to the user. When the user clicks OK, the GetOption function returns a value between 1 and 12. If the user clicks Cancel, the function returns False,

View or Download

Click the link below to download modGetOption.Bas. This is a text file that can be imported directly into a VBA project. In the VB Editor, activate your project, then select File Import File (or press Ctrl+M). Locate the modGetOption.Bas file and click OK. The module will be added to your project.

NOTE: It is not necessary to add a reference to the Visual Basic Extensibility Library in order to use this function.

Disabling A Userform’s Close Button

Category: UserForms | [Item URL]

Every UserForm that you create contains a Close button ("x") in its title bar. Clicking the Close button (or pressing Alt+F4) closes the UserForm.

In some cases, you may not want to allow the user to close the UserForm in this manner. There's no easy way to disable the Close button, but a few lines of VBA code will prevent it from being used.

Activate the code module for your UserForm, and enter the following procedure:

Private Sub UserForm_QueryClose _
  (Cancel As Integer, CloseMode As Integer)
'   Prevents use of the Close button
    If CloseMode = vbFormControlMenu Then
        MsgBox "Clicking the Close button does not work."
        Cancel = True
    End If
End Sub

When the user clicks the Close button, a message box appears and the UserForm remains open.

Using Controls On Worksheets

Category: General VBA / UserForms | [Item URL]

A companion file is available: Click here to download

Most developers know that it's possible to insert dialog box controls (such as buttons and checkboxes) directly on a worksheet. This topic, however, is the source of a great deal of confusion. This tip attempts to clarify some of the issues related to using controls on worksheets.

Two types of controls

First, it's important to understand that Excel supports two general types of controls:

• ActiveX Controls
• Excel Controls

These two types of controls are described in the table below.

	ActiveX Controls	Excel Controls
Excel versions	97, 2000	5, 95, 97, 2000
Which toolbar?	Control Toolbox	Forms
Controls available	CheckBox, TextBox, CommandButton, OptionButton, ListBox, ComboBox, ToggleButton, SpinButton, ScrollBar, Label, Image	Label, GroupBox, Button, CheckBox, OptionButton, ListBox, ComboBox, ScrollBar, Spinner
Macro code storage	In the code module for the Sheet	In any standard VBA module
Macro name	Corresponds to the control name (e.g., CommandButton1_Click)	Any name you specify.
Correspond to...	UserForm controls	Dialog Sheet controls
Customization	Extensive, using the Properties box	Minimal
Respond to events	Yes	Click or Change events only

Using ActiveX controls

To insert an ActiveX control on a worksheet, make sure the Control Toolbox toolbar is displayed. Then click the desired control, and drag in the sheet to create the control. After adding a control, Excel is in "design mode." To make any changes to the control, Excel must be in design mode. To test the control, Excel must not be in design mode. Use the Design Mode button on the Control Toolbox toolbar to toggle design mode.

To customize the control, right-click it and select Properties. This displays the Properties window. Each control has its own set of properties.

Some ActiveX controls can be connected to a cell (i.e., the LinkedCell property). Other controls can receive information from a range. For example, a ListBox control can get its list data from the ListFillRange property. Consult the online help for complete details regarding the properties for each control.

Each ActiveX control also triggers events. For example, a CommandButton control generates a Click event. You can write an event-handler Sub procedure to respond to the events. The procedure below, for example, displays a message box when a CommandButton (named CommandButton1) is clicked:

Private Sub CommandButton1_Click()
    MsgBox "CommandButton1 was clicked."
End Sub

Event-handler procedures must be located in the code module for the sheet on which the control is placed. It's important to understand that you cannot assign an arbitrary macro to an ActiveX control. To access the correct code module, right-click the control and choose View Code. The code module displays two drop-down controls at the top. Use these controls to determine the events supported for your control.

Using Excel controls

To insert an Excel control on a worksheet, make sure the Forms toolbar is displayed. Then click the desired control and drag in the sheet to create the control.

Right-click the control to access the Format dialog box. Any customizations to the control are made using this dialog box. Formatting options are limited. To assign a macro to the control, right-click it and select Assign Macro. Excel controls can execute any macros.

Developer FAQ - UserForms

Category: UserForms | [Item URL]

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?

No.

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:

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, 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.

Page 2 of 2 pages
[Previous page]