Sep 8 2009

Working with Userforms Part 1: Hello World! Button

Over the next few posts we will look at how to build a userform and put it to good use.  Using a userform will provide additional functionality in your project that using the spreadsheet alone may not provide.  One way to give your project that customized polished look, may be to incorporate the use of userforms.

[ad#in-post-ad]

How To Add a Userform to your Project
There are many ways to achieve the next few steps I’m about to show you, but this is one way that I think is pretty straight forward.  In this example, we will add a userform to our project with a small button that will say “Hello World!” when clicked.  We will build upon this example in future posts.

  1. To access the VBA portion of Microsoft Excel just hit ALT+F11 while Excel is active (Hint: you can hit ALT+F11 to switch between the code editor and your spreadsheet at anytime to quickly switch windows). 
  2. From the file menu at the top, select Insert–>Userform.  A blank userform will appear.
  3. If the toolbox isn’t already displayed on your screen, hit View–>Toolbox and it will appear.
  4. Select the button icon from the toolbox and use it to draw you button onto the userform. 
  5. Change the text of the button by clicking on it and typing whatever you want the button to say.
  6. Now for the tricky part – to add the code telling the button what to do when clicked, double click on the button to access its “_Click” event code.  A window should open up with code that looks like this:

Private Sub CommandButton1_Click()

End Sub

  1. Now let’s add our message box to pop up with the text for hello world. Your vba should look like this:

Private Sub CommandButton1_Click()
MsgBox (“Hello World!”)
End Sub

  1. Now we need to add a button to click to call the userform we want to show. So add a button to your spreadsheet and change the text to “Userform Test”. (Add the “Forms” toolbar to your visible toolbars if you haven’t already; the buttons control is located on that toolbar)
  1. Hit ALT+F11 one more time to go back to your code. This time we want to insert a macro by selecting from the file menu: INSERT–>MODULE. An empty macro should appear.
  1.   Replace the macro with this code (more info on getting started with macros):

Sub TestMacro()
UserForm1.Show
End Sub

  1.   Now go back to the button you added to the spreadsheet. Right-click and select “Assign Macro”. Select “TestMacro” from the list and click OK.
  1.   Finally, click off the button into a cell and then click your newly created button. You should see your userform pop up with a button you can click on. Click the button and a “Hello World!” message will appear something like the screenshot below. That’s It!!
Userform Example

Userform Example

[ad#in-post-ad]

Going Forward with Userforms
We just built a simple example showing how to use a userform. Although this is just the starting point, hopefully you can see the possibilities that adding this to your skillset can provide. Adding comboboxes, listboxes, textbox, etc. to your userform is just around the corner. The goal is to provide the best user experience as possible – and adding userforms can be a step in the right direction.

More Excel Tips

Did you find this post helpful? Try signing up for our free daily tips.

Enter your email address: 

          
TAGS: ,

2 Comments on this post

Trackbacks

  1. SANDY HANBERG said:

    THIS HAS NOTHING TO DO WITH THE USERFORM.

    WHY CANT I DO THIS ‘Sub TEST1() DOES NOT WORK
    Sub TEST1()
    With Worksheets(“Sudocu”).[A1]
    If .Text = “” _
    AND [A1:A9] “1” _
    AND [A1:I1] “1” _
    AND [A1:C1, A2:C2, A3:C3] “1” THEN
    [A1] = “1”
    End If
    End With
    End Sub

    INSTEAD OF THIS ‘Sub TEST2() WORKS
    Sub TEST2()
    With Worksheets(“Sudocu”).[A1]
    If .Text = “” _
    And [A1] “1″ And [A2] “1″ And [A3] “1″ _
    And [A4] “1″ And [A5] “1″ And [A6] “1″ _
    And [A7] “1″ And [A8] “1″ And [A9] “1″ _
    And [A1] “1″ And [B1] “1″ And [C1] “1″ _
    And [D1] “1″ And [E1] “1″ And [F1] “1″ _
    And [G1] “1″ And [H1] “1″ And [I1] “1″ _
    And [A1] “1″ And [B1] “1″ And [C1] “1″ _
    And [A2] “1″ And [B2] “1″ And [C2] “1″ _
    And [A3] “1″ And [B3] “1″ And [C3] “1″ Then
    [A1] = “1”
    End If
    End With
    End Sub

    SANDY HANBERG
    847-966-2299
    SANDYGERRI@NETZERO.COM

    September 13th, 2009 at 5:30 pm
  2. SANDY HANBERG said:

    WHY CANT I DO THIS ‘Sub TEST1()
    Sub TEST1()
    With Worksheets(“Sudocu”).[A1]
    If .Text = “” _
    AND [A1:A9] “1” _ ‘NOT EQUAL SIGN IS NOT VISIBLE.
    AND [A1:I1] “1” _ ‘NOT EQUAL SIGN IS NOT VISIBLE.
    AND [A1:C1, A2:C2, A3:C3] “1” THEN ‘NOT EQUAL SIGN IS NOT VISIBLE.
    [A1] = “1”
    End If
    End With
    End Sub

    INSTEAD OF THIS ‘Sub TEST2()
    Sub TEST2()
    With Worksheets(“Sudocu”).[A1]
    If .Text = “” _
    And [A1] “1″ And [A2] “1″ And [A3] “1″ _’NOT EQUAL SIGN IS NOT VISIBLE.
    And [A4] “1″ And [A5] “1″ And [A6] “1″ _’NOT EQUAL SIGN IS NOT VISIBLE.
    And [A7] “1″ And [A8] “1″ And [A9] “1″ _’NOT EQUAL SIGN IS NOT VISIBLE.
    And [A1] “1″ And [B1] “1″ And [C1] “1″ _’NOT EQUAL SIGN IS NOT VISIBLE.
    And [D1] “1″ And [E1] “1″ And [F1] “1″ _’NOT EQUAL SIGN IS NOT VISIBLE.
    And [G1] “1″ And [H1] “1″ And [I1] “1″ _’NOT EQUAL SIGN IS NOT VISIBLE.
    And [A1] “1″ And [B1] “1″ And [C1] “1″ _’NOT EQUAL SIGN IS NOT VISIBLE.
    And [A2] “1″ And [B2] “1″ And [C2] “1″ _’NOT EQUAL SIGN IS NOT VISIBLE.
    And [A3] “1″ And [B3] “1″ And [C3] “1″ Then’NOT EQUAL SIGN IS NOT VISIBLE.

    [A1] = “1”
    End If
    End With
    End Sub

    SANDY HANBERG
    847-966-2299
    SANDYGERRI@NETZERO.COM

    September 13th, 2009 at 5:38 pm

LEAVE A COMMENT

Subscribe Form

Subscribe to Blog

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks

Categories

Archives