ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Software»
  • Office Software Suites»
  • Microsoft Office

How to write Visual Basic code to configure a User Interface created using a UserForm in Excel 2007 and Excel 2010

Updated on August 17, 2013

Using Visual Basic to Initialise, Control and Configure a UserForm in Excel 2007 and Excel 2010

UserForms allow you to design and create fully customisable dialogue boxes for users of your workbooks. They enable you to:

  • Design a User Interface that is easy and intuitive for users to use
  • Reduce the amount of manual data entry required when entering data and also the number of errors in your data
  • Control data entry by error checking data before it is entered into your workbook
  • Finally, a well designed UserForm is visually impactful and will make the workbook look professional

However, a UserForm without Visual Basic controlling the configuration of all the controls you have added (combo boxes, command buttons etc.) will have no functionality and the user will be unable to interact with it, or use it.

Before beginning to code a UserForm, you will need to create one. I have a hub in which I create a Hotel Reservation Interface which uses several controls including Command Buttons, Combo Boxes, Text boxes, Spin Bars and finally Option and Radio Buttons. That hub can be found here:

http://robbiecwilson.hubpages.com/hub/User-Interface-design-using-a-UserForm-in-Excel-2007-and-Excel-2010


In this hub, we will write a number of scripts:

  • First for when the UserForm is initialized (in other words when the user clicks on the Reservations button which launches our Hotel Reservation Interface and therefore the UserForm)
  • Next, we need a script for when a user clicks the Add Another Reservation button
  • A further script is needed for when the user selects the Close Window button
  • Finally, we need a script for the Scroll Bar so that the user’s selection is displayed on screen and also stored so that it can be entered into the worksheet along with the other details of the reservation

Example of a Hotel Reservation User Interface created with Visual Basic and a UserForm in Excel 2007 or Excel 2010.
Example of a Hotel Reservation User Interface created with Visual Basic and a UserForm in Excel 2007 or Excel 2010. | Source
Example of the output from a UserForm in a worksheet in Excel 2007 or Excel 2010.
Example of the output from a UserForm in a worksheet in Excel 2007 or Excel 2010. | Source

Creating an Initialisation script in Visual Basic for a UserForm in Excel 2007 and Excel 2010

To access the code assigned to our UserForm,

  • On the Developer tab click the View Code button in the Controls Group
  • Scroll down to Forms and double click on the UserForm you created

Adding Visual Basic code to a UserForm in Excel 2007 or Excel 2010.
Adding Visual Basic code to a UserForm in Excel 2007 or Excel 2010. | Source

When you double click the UserForm it will open up the code page. Change the first line to the following and then add all the subsequent lines:

Private Sub UserForm_Initialize()

Nationality.Value = ""

Names.Value = ""

With Guests

.AddItem "1"

.AddItem "2"

.AddItem "3"

.AddItem "4"

.AddItem "5"

End With

CarNo = True

BreakfastNo = True

Nights.Max = 20

End Sub

The figure below shows the code with remarks on each line to show what that line does.

Initialisation script for a UserForm created in Visual Basic in Excel 2007 or Excel 2010.
Initialisation script for a UserForm created in Visual Basic in Excel 2007 or Excel 2010. | Source

The script does a number of things:

  • First, it resets the Nationality and Name textboxes back to blanks
  • Then it configures the Combo Box for the number of guests
  • Next it resets the Option or Radio buttons for whether a guest has a Car or wants Breakfast to No
  • Finally, it sets the maximum number of Nights a guest can stay to 20

If you are a complete beginner in Visual Basic and you would like to know more about how to get started writing basic code, I have a hub that starts at the very beginning and introduces a number of useful commands as well as how to deal with errors which can be found here:

http://robbiecwilson.hubpages.com/hub/Visual-Basic-for-Excel-2007-and-Excel-2010-A-Beginners-Guide

Adding Visual Basic code to Command Buttons in Excel 2007 and Excel 2010

Our Hotel Reservation User Interface uses two Command Buttons, the Add Another Reservation button and the Close button. These scripts will be run when the user clicks on one of the buttons

First, we will look at the script that activates when the user clicks Add Another Reservation

Private Sub CommandButton1_Click()

Sheets("Reservations").Activate

Dim sourceCol As Integer, rowCount As Integer

Dim currentRow As Integer

Dim currentRowValue As String

sourceCol = 1

rowCount = Cells(Rows.Count, sourceCol).End(xlUp).Row

For currentRow = 1 To rowCount

currentRowValue = Cells(currentRow, sourceCol).Value

If IsEmpty(currentRowValue) Or currentRowValue = "" Then

Cells(currentRow, sourceCol).Select

End If

Next

Cells(currentRow, 1).Value = Names.Value

Cells(currentRow, 2).Value = Nationality.Value

Cells(currentRow, 3).Value = Guests.Value

Cells(currentRow, 4).Value = Number_of_Nights.Value

If CarYes.Value = True Then

Cells(currentRow, 5).Value = "Yes"

End If

If CarNo.Value = True Then

Cells(currentRow, 5).Value = "No"

End If

If BreakfastYes.Value = True Then

Cells(currentRow, 6).Value = "Yes"

End If

If BreakfastNo.Value = True Then

Cells(currentRow, 6).Value = "No"

End If

Nationality.Value = ""

Names.Value = ""

CarNo = True

BreakfastNo = True

Guests = “”

Nights.Value = 0

Number_of_Nights.Value = ""

End Sub

Visual Basic script used to configure a command button in a UserForm in Excel 2007 or Excel 2010.
Visual Basic script used to configure a command button in a UserForm in Excel 2007 or Excel 2010. | Source

This script is the most complicated that we will write for our Hotel Reservation User Interface. It is divided into four sections, as shown in the figure above:

  • The first part sets up the variables we will use to find the first available blank row and tells Excel which sheet we are working off.
  • The second looks for the first empty row to add the next reservation. This ensures that a reservation is never over written
  • The third part adds the data that has been entered into the spreadsheet
  • The final part re-initiliases the UserForm so that a new client can be added without having to clear the details of the previous client

The next script is used when the user selects the Close Window button

It reuses the code to add the details of the client’s reservation into Excel that we wrote for the initialisation script and then it closes the UserForm

Private Sub CommandButton2_Click()

Sheets("Reservations").Activate

Dim sourceCol As Integer, rowCount As Integer

Dim currentRow As Integer

Dim currentRowValue As String

sourceCol = 1

rowCount = Cells(Rows.Count, sourceCol).End(xlUp).Row

For currentRow = 1 To rowCount

currentRowValue = Cells(currentRow, sourceCol).Value

If IsEmpty(currentRowValue) Or currentRowValue = "" Then

Cells(currentRow, sourceCol).Select

End If

Next

Cells(currentRow, 1).Value = Names.Value

Cells(currentRow, 2).Value = Nationality.Value

Cells(currentRow, 3).Value = Guests.Value

Cells(currentRow, 4).Value = Number_of_Nights.Value

If CarYes.Value = True Then

Cells(currentRow, 5).Value = "Yes"

End If

If CarNo.Value = True Then

Cells(currentRow, 5).Value = "No"

End If

If BreakfastYes.Value = True Then

Cells(currentRow, 6).Value = "Yes"

End If

If BreakfastNo.Value = True Then

Cells(currentRow, 6).Value = "No"

End If

Unload Me

End Sub

Visual Basic script used to configure a button to close a UserForm in Excel 2007 or Excel 2010.
Visual Basic script used to configure a button to close a UserForm in Excel 2007 or Excel 2010. | Source

As before, the above figure shows the code, along with comments on each line illustrating what that line does.

Adding Visual Basic code to a Scroll Bar in a UserForm in Excel 2007 and Excel 2010

The final script that we have to write is a script that captures the value the user selects with the scroll bar and stores this in a variable so that this can be displayed in the text box alongside the scroll bar and added along with the other reservation details into the workbook:

Private Sub Nights_Change()

Number_of_Nights.Text = Nights.Value

End Sub

Create a Command Button to launch a UserForm in Excel 2007 and Excel 2010

To initiate our Hotel Reservation system, we need a button for the user to click when they want to add a reservation to the Excel workbook. To do this, we will use a Command button:

  • On the Developer tab, select the Insert button from the Controls group
  • Under ActiveX Controls, select Command Button
  • The cursor will change to a cross, use this to position and size the new button

Command button created in Excel 2007 or Excel 2010.
Command button created in Excel 2007 or Excel 2010. | Source
  • Next, select the Design Mode button
  • Right click on the new button and select Properties
  • Select Caption and change CommandButton1 to Reservations
  • Change Name to Reservations

Configuring a Command button for use with a UserForm in Excel 2007 or Excel 2010.
Configuring a Command button for use with a UserForm in Excel 2007 or Excel 2010. | Source

If you would like to learn more about how to create, configure and use Command Buttons, I have a hub dedicated to them which can be found here:

http://robbiecwilson.hubpages.com/hub/Create-a-Command-Button-in-Excel-2007

Now we need to add the Visual Basic code to our button so that it launches the reservation system when it is clicked:

  • Select the button once more
  • Click the View Code button

You will see the following text

Private Sub Reservations_Click()

End Sub

  • Add the following on a line between those two lines of code

Reservations_Interface.Show

You will end up with the following:

Visual Basic script used to configure a Command button to launch a UserForm in Excel 2007 or Excel 2010.
Visual Basic script used to configure a Command button to launch a UserForm in Excel 2007 or Excel 2010. | Source
  • Close the Visual Basic window
  • Select Design Mode once more
  • Test that the button launches our Reservation Interface

Conclusion

Being able to code in Visual Basic enables you to extend what Excel can do to more closely meet your and your user’s needs. Today, we looked at creating code to bring a User Interface created using a UserForm to life. Users of my spreadsheet will now have a fully functional Hotel Reservation Interface which will allow them to enter a customer’s reservation details into Excel quickly and easily and with fewer errors. These details will then be automatically added into a worksheet.

In today’s hub:

  • We created a script that sets up our User Interface for use (configures all the buttons and bars)
  • Next, we wrote scripts for our Command Buttons to allow users to add another reservation or close the Interface
  • Code was added to a Scroll Bar to store and display the user’s selection
  • Finally, we added a Command Button to our workbook to launch the User Interface

Many thanks for reading; I hope that you have found this hub useful and informative. I learnt a lot during the research of this hub and also writing the code for my Hotel Reservation Interface. Please feel free to leave a comment below and happy coding!

© 2013 Robbie C Wilson

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.