Adding A Hyperlink To A Userform
A companion file is available: Click here to downloadYou 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!)
- Add a Label object and enter some text for its Caption
- Make the Label blue and underlined so it resembles a typical hyperlink. You might also want to set its font to Courier New.
- None of the standard mouse pointers resembles a pointing hand, so set the Label's MousePointer property to: 99 - fmMousePointerCustom
- 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.
- 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:firstname.lastname@example.org"
Creating A Userform Programmatically
A companion file is available: Click here to downloadMany 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:
- It creates a new UserForm.
- It adds OptionButton controls to the UserForm, the number of which corresponds to the number of elements in the OpArray argument.
- It adds two CommandButton controls to the UserForm (OK and Cancel).
- It creates two subroutines to handle the Click events for the CommandButtons.
- Adjusts the size of the UserForm to accommodate the OptionButtons.
- It displays the UserForm and waits for the user's response.
- It returns the index number of the selected item to the calling procedure -- or it returns False if the UserForm was canceled.
- 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
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
Using Controls On Worksheets
A companion file is available: Click here to downloadMost 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
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