VBA TUTOR NEWSLETTER ~ TUTORIAL TWELVE: CREATING A FONT UNDERLINE USERFORM

>GRSoftware >VBA Tutor >Newsletter Tutorials >Tutorial 11 >Tutorial 13

VBA TUTOR NEWSLETTER ~ TUTORIAL TWELVE:

Creating a font underline UserForm
to replace the standard underline button

Now that a single line under text has become a standard to indicate a link, it is time to use different underlining to add emphasis to text. We are going to create a userform that allows you to select the underline style you want by creating a macro that replaces the standard underline command.

Notice that OptionButton's are used to select the underline style, not a listbox. Why? Try both methods. You will find that it is a lot quicker to view and select options using OptionButtons!

STEP ONE: Create a module to contain the code.

In the VB Editor, open the Project Explorer window using "View | Project Explorer", select the 'Normal' project, insert a new module using "Insert | Module", then declare the public variable 'underlinetype' at the top of this new module.

Option Explicit
Public underlinetype As String

STEP TWO: Create the new macro.

In the newly created module, create a macro called 'Underline' that loads and opens a userform called 'Underlinedlg'.

Sub Underline()
'reset underlinetype to null [You must ALWAYS reset variables!]
underlinetype = ""
Load Underlinedlg
Underlinedlg.Show
End Sub

STEP THREE: Create the UserForm.

With the 'Normal' project selected in the Project Explorer, create a UserForm using "Insert | UserForm". Open its properties sheet from the right click menu and change its name to 'Underlinedlg'.

Next, open the Toolbox and select the 'OptionButton' tool. On the UserForm grid, 'Draw' a OptionButton so it is 15 by 5 grid squares, then copy and paste this OptionButton to create a total of nine OptionButtons. Use the 'Align' options in the right click menu to align them.

Now, select the 'CommandButton' tool from the Toolbox Dialog and 'Draw' two CommandButtons. Finally, use the UserForm's properties sheet to make it look something like this ...

Underlinedlg userform

STEP FOUR: Create the code for the two CommandButtons.

Either double click on the CommandButton in design mode or select from the right click menu to view the code for that CommandButton, then copy and paste the following code.

The code for the 'Ok' CommandButton is ...

Private Sub CommandButton1_Click()
'if no OptionButton selected then exit
If underlinetype = "" Then
MsgBox "You Must first select a Underline Type!"
Exit Sub
End If
Selection.Font.Underline = underlinetype
Underlinedlg.Hide
Unload Underlinedlg
End Sub

The code for the 'Remove Underline' CommandButton is ...

Private Sub CommandButton2_Click()
Selection.Font.Underline = wdUnderlineNone
Underlinedlg.Hide
Unload Underlinedlg
End Sub

STEP FIVE: Create the code for the nine OptionButtons.

Use the same method to enter the code for the CommandButtons to enter the code for the OptionButtons.

The code for the 'Single' OptionButton is ...

Private Sub OptionButton1_Click()
underlinetype = wdUnderlineSingle
End Sub

Use the online help to set the 'underlinetype' values for the other OptionButtons.

STEP SIX: Save the code!

NOTE: In a userform you can only have one OptionButton selected in a group of OptionButtons, the coding for this being done automatically. If you wanted to make multiple selections, you would use CheckBoxes instead.

HOPEFULLY, THESE CODE EXAMPLES WILL ADD TO YOUR ABILITY TO BECOME A VBA POWER USER!