TASK 5.1 - Creating and Using a UserForm

The basic techniques for doing this are covered in the relevant section of the notes. You should first go there and make sure you are familiar with this material.

TASK 5.2 - Checking you have got the basic idea!

In this exercise, we will create a simple custom dialog box. There are three necessary stages to using a custom dialog box: designing the interface, accessing it from within VBA code, and making use of the results from the form. In this exercise we will develop an extremely simple custom dialog box to illustrate these three steps.

Open a new Excel workbook, start the VBA Editor, and select "Insert", "UserForm" from the VBA menu.

You should find that two new windows appear, the UserForm itself, and a control toolbox:

The idea now is to use the controls in the toolbox to create the required user interface for your custom dialog box. For this example, we will keep it very si 848k1020i mple! Add controls to create this form:

The caption of the form may be changed from the Properties window, the text is a label control and the other controls are a text control and two commandbutton comtrols. The contents of the label and the captions of the buttons can be edited by double clicking on them.

Test out the dialog box by clicking on the RUN button, or pressing F5.

Now you need to make the dialog box do something! We will aim to access the dialog box from the worksheet by clicking on a button. The user should then type something into the text box and click OK. The contents of the textbox will then be displayed from the calling procedure on the worksheet.

First, let's get the dialog box to do something. Double-click on the OK button to access its code procedure, and put this code into it:

MsgBox TextBox1.Text

Unload Me

Now try running the program again.

Put some code into the CLEAR button's click procedure to erase the contents of the textbox:

TextBox1.Text = ""

Next, we need to be able to summon this dialog box from the worksheet. Double click on the Sheet1 item in the Project window, and locate the General section in the left drop-down list box. Type in to the window this code:

Sub RunDialog()

UserForm1.Show

End Sub

Test it out again!

The last thing we need to do surrounds the transfer of information between the dialog box and the worksheet. Clearly in order for a dialog box to be of much use, any information entered into it must be available to the rest of your application, whether that means code in worksheet-level procedures or in workbook modules. As things stand the contents of the textbox are only available within the UserForm itself. To make the information public, you declare the relevant variables to be Public within a code module.

From the Project window, add a Module to your Project. Place this code in the General section of the module:

Public msg as string

In the Userform, you need to replace

MsgBox TextBox1.Text

Unload Me

with

Msg = TextBox1.Text

Unload Me

and back in the RunDialog procedure, you can now access this variable:

Sub RunDialog()

UserForm1.Show

MsgBox "The contents of the textbox is " + msg

End Sub

Now try modifying the code so that if a blank string was entered some informative message appears.

TASK 5.3 - Using the Full Set of Dialog Box Controls

This example will demonstrate how to use the controls available to you on a UserForm.

The first step is to start a new workbook, enter the VBA editor and add a UserForm. You can, if you wish, rename this UserForm to call it something meaningful.

You should add controls and text to create a user interface like this:

Note that there are two blank labels below the "Value" labels next to the Spinner and ScrollBar controls.

Here is the code to be attached to the various elements of this form - note that some of the control names have been changed to help identify which is which. I'll leave it to your common sense to figure out where this is!

Private Sub CommandButton1_Click()

If CommandButton1.Caption = "Hide Options" Then

CommandButton1.Caption = "Show Options"

Frame1.Visible = False

Else

CommandButton1.Caption = "Hide Options"

Frame1.Visible = True

End If

End Sub

Private Sub CommandButton2_Click()

nl = Chr(10)

msg = ""

msg = msg + "Item " + Format(ComboBox1.ListIndex + 1) + " selected : " + ComboBox1.Text

If TextBox1.Text <> "" Then msg = msg + nl + "Contents of textbox: " + TextBox1.Text

If ExtraOptions1.Value = True Then msg = msg + nl + "Extra Options#1 selected"

If ExtraOptions2.Value = True Then msg = msg + nl + "Extra Options#2 selected"

If ExtraOptions3.Value = True Then msg = msg + nl + "Extra Options#3 selected"

If CheckBox1.Value = True Then msg = msg + nl + "Check Box 1 checked"

If CheckBox2.Value = True Then msg = msg + nl + "Check Box 2 checked"

If OptionButton1.Value = True Then msg = msg + nl + "Option Button 1 selected"

If OptionButton2.Value = True Then msg = msg + nl + "Option Button 2 selected"

If OptionButton3.Value = True Then msg = msg + nl + "Option Button 3 selected"

msg = msg + nl + "Spinner Value is " + SpinnerVal.Caption

msg = msg + nl + "Scroll Bar Value is " + ScrollBarVal.Caption

MsgBox msg, vbInformation + vbOKOnly, "Dialog Values"

End Sub

Private Sub CommandButton3_Click()

Unload Me

End Sub

Private Sub ScrollBar1_Change()

ScrollBarVal.Caption = Format(ScrollBar1.Value)

End Sub

Private Sub ScrollBar1_Scroll()

ScrollBarVal.Caption = Format(ScrollBar1.Value)

End Sub

Private Sub SpinButton1_Change()

SpinnerVal.Caption = Format(SpinButton1.Value)

End Sub

Private Sub UserForm_Initialize()

For k = 1 To 26

ComboBox1.AddItem Chr(64 + k)

Next

ComboBox1.ListIndex = 0

SpinnerVal.Caption = "0"

ScrollBarVal.Caption = "0"

End Sub

The UserForm is 'set up' in the Initialize procedure - this is automatically run whenever the form is loaded and allows you to determine the initial state of the form. Here we have added the 26 capital letters to the ComboBox - these are taken from the standard list of ASCII characters, which starts with A at number 65 in the sequence. Look on the help file for the full set.

You can see the finished article here.

TASK 5.4 - Another Example of UserForms - Car Costs

This exercise shows another example of the use of a UserForm. The best plan is to run the spreadsheet and then look at the code.

There are two worksheets, each containing fuel records for one car. The idea of the exercise is to find out the fuel cost and mpg over a particular interval. The code will determine the date range of the worksheet and then present a custom dialog box to the user, from which they select the required date range. When the user clicks OK the code calculates the relevant statistics and presents the answer in fields provided on the dialog box. This form is persistent - in that it remains visible - until the user clicks CANCEL.

Upon clicking OK, the text fields are completed, as seen in the next figure.

Your job:

Simplify the code (and the userform) , so that the date ranges are restricted to financial years - i.e. 1^st April - 31^st March.

TASK 5.5 - Another Example of UserForms - A Program Launcher

This example shows how to use a custom dialog box to select an external program to launch.

You need to create a new workbook, and design a new UserForm to look like this:

The following code is attached to this form:

Private Sub cmdCancel_Click()

Unload Me

End Sub

Private Sub cmdDeleteItem_Click()

zIndex = ListBox1.ListIndex

If zIndex < 0 Then Exit Sub

Open ThisWorkbook.Path + "\launcher.dat" For Output As #1

For j = 0 To nitem - 1

If j <> zIndex Then

a$ = ListBox1.List(j)

b$ = progpath$(j + 1)

Write #1, a$, b$

End If

Next j

Close #1

UserForm_Initialize

End Sub

Private Sub cmdNewItem_Click()

a$ = txtProgDesc.Text

b$ = txtProgPath.Text

If a$ = "" Or b$ = "" Then

MsgBox "You must fill in both text boxes!"

Else

Open ThisWorkbook.Path + "\launcher.dat" For Append As #1

Write #1, a$, b$

Close #1

txtProgDesc.Text = ""

txtProgPath.Text = ""

UserForm_Initialize

End If

End Sub

Private Sub cmdOK_Click()

zIndex = ListBox1.ListIndex + 1

If zIndex > 0 Then Shell progpath$(zIndex), 1

End Sub

Private Sub UserForm_Initialize()

nitem = 0

txtProgDesc.Text = ""

txtProgPath.Text = ""

With ListBox1

.Clear

Open ThisWorkbook.Path + "\launcher.dat" For Input As #1

While Not EOF(1)

Input #1, a$, b$

.AddItem a$

nitem = nitem + 1

progpath(nitem) = b$

Wend

Close #1

End With

End Sub

And this needs to be added to a new code module:

Public progpath(100) As String

Public nitem As Integer

A text file (launcher.dat) needs to be placed in the same directory as the workbook, and contain something similar to this:

"NOTEPAD","notepad.exe"

"WRITE","write.exe"

"CONTROL PANEL","control.exe"

"REG EDIT","regedit.exe"

"SOLITAIRE","sol.exe"

The only other thing you need to do is to create a button on the worksheet to invoke the dialog box.     The result will look like that in the next figure.

You will note that it is possible to add and delete items from the list, so you can add new programs as and when necessary.

Your job:

Can you modify this program so that you can edit the values of the program description and path of the items already available? This would be more convenient than deleting and adding items.

Secondly, can you provide controls to alter the order in which the items appear in the list?

Thirdly, can you provide a way of putting the items in alphabetical order, and offering this to the user as an option?