ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

User Interface design using a UserForm in Excel 2007 and Excel 2010

Updated on August 17, 2013

Guide to creating a User Interface or Dialogue Box in Excel 2007 and Excel 2010 using a UserForm

Hi and welcome to my latest hub on Excel. Today, we will be looking at creating a User Interface using a UserForm. Every time Excel opens up a window and asks you to input data, this is done using a UserForm. Excel allows you to create your own so that you can easily collect data from users of your spreadsheet. Every UserForm uses Visual Basic to enable users to interact with it. Visual Basic is used to:

  • Add functionality to the UserForm
  • To configure the various options made available to users of our User Interface, and finally,
  • Handle a users input into the UserForm, for example, to copy the user’s input into an Excel worksheet

UserForms have several advantages:

  • They are easy and intuitive for users to use
  • UserForms reduce the amount of manual data entry and the number of errors
  • Data entry is controlled and can be error checked by the UserForm so that the data is entered into your workbook in a consistent and accurate manner
  • A well designed UserForm is visually impactful and will make the workbook look professional

In today’s hub, we will be creating our own UserForm for my hotel business. We will be using several controls in the dialogue box, including:

  • Textboxes
  • Combo Boxes
  • Option / Radio buttons
  • Spin Buttons

In addition, the UserForm will be launched when the user clicks on a Command Button. The figures below show first of all the Command Button used to launch our Reservation System as well as the Reservation Interface and then the reservations entered into Excel using the interface:

User Interface for Hotel Reservations created using a UserForm in Excel 2007 and Excel 2010.
User Interface for Hotel Reservations created using a UserForm in Excel 2007 and Excel 2010. | Source
Data entered into an Excel 2007 or Excel 2010 worksheet automatically from a UserForm.
Data entered into an Excel 2007 or Excel 2010 worksheet automatically from a UserForm. | Source

The UserForm is run by a number of Visual Basic scripts. These scripts configure all the controls we have added and also add functionality to them. Without Visual Basic code, the user would not be able to interact with our User Interface. The scripts we will add to the UserForm include:

  • One which runs when the UserForm is initiated and configures how all buttons and boxes the user can interact with
  • Another allows the user to add another record
  • Finally, there is one to close the User Interface when the user clicks close

These scripts are covered in a separate hub that covers all of the scripts in great detail:

http://robbiecwilson.hubpages.com/hub/How-to-write-Visual-Basic-code-to-configure-a-User-Interface-created-using-a-UserForm-in-Excel-2007-and-Excel-2010

I have hubs covering each of the above Controls:

Command Buttons allow you to add code to a button so that when a user presses it, the code is executed. To learn more about Command Buttons, my hubs on them can be found here:

http://robbiecwilson.hubpages.com/hub/Configuring-and-Using-Forms-created-using-ActiveX-List-Boxes-in-Excel-2007-and-Excel-2010

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

Option or Radio buttons allow a user to select one option from a list and are perfect for Yes / No On / Off selections as we use here. I cover them in far greater detail in my hub here:

http://robbiecwilson.hubpages.com/hub/Using-Form-Controls-and-ActiveX-Controls-Option-Buttons-or-Radio-Button-in-Excel-2007-and-Excel-2010

Spin Buttons allow you to quickly and easily select a number from a range. You can define the range and also the behaviour of the slider and the button. Users find them easy and intuitive to use.

http://robbiecwilson.hubpages.com/hub/Creating-configuring-and-using-Form-Controls-and-ActiveX-Controls-Spin-Buttons-in-Excel-2007-and-Excel-2010

Combo Boxes enable you to create a box with a configurable drop down list, from which a user can select either one or multiple items. To learn more about these powerful and useful controls:

http://robbiecwilson.hubpages.com/hub/Configuring-and-Using-Combo-boxes-in-Excel-2007

Adding the Developer tab in Excel 2007 and Excel 2010

Before we can create our UserForm, we need to ensure that the Developer tab is visible in Excel. If you need to add the tab, the instructions are different depending on whether you are using Excel 2007 or Excel 2010.

For Excel 2007:

  • Navigate to the Excel button
  • Choose Excel Options
  • On the Popular tab, ensure that Show Developer tab in the Ribbon is clicked
  • Select OK

Adding the Developer tab in Excel 2007.
Adding the Developer tab in Excel 2007. | Source

For Excel 2010:

  • Go to the File menu
  • Select Options
  • Choose the Customize Ribbon tab
  • Check Developer under Main Tabs as shown below

How to add the Developer tab in Excel 2010.
How to add the Developer tab in Excel 2010. | Source

Creating a UserForm in Excel 2007 and Excel 2010

To begin, we need to create the UserForm that our reservations team will use to enter the data for the customer’s reservation into. To do this:

  • Click the View Code button on the Developer tab
  • Select the Insert menu and then UserForm
  • This will create our blank UserForm

Blank UserForm created in Excel 2007 and Excel 2010.
Blank UserForm created in Excel 2007 and Excel 2010. | Source
  • First, we rename the Userform by changing the (Name) in the Properties in the panel on the left from UserForm1 to Reservation_Interface
  • Next, we need to change the Caption (again in Properties) to Reservation Interface Version 1.0

Now we need to create the boxes that our reservations team will enter the client’s details into:

  • We now add a Label from the Toolbox to name our UserForm and enter text into the box by clicking on it (I added Reservation System for Wilson's Hotel Bournemouth)
  • We can change the font by clicking on the label and then Properties and selecting Font.
  • Next we add the labels for Name, Nationality, Number of Guests, Number of Nights, Car and Breakfast

Note: to create a number of identical labels, right click the first one and select Copy and then Paste

UserForm with Text Boxes added in Excel 2007 or Excel 2010.
UserForm with Text Boxes added in Excel 2007 or Excel 2010. | Source

The next step is to add the controls to our UserForm so that information can be entered about the client into the UserForm:

  • For Name, Nationality and Number of Nights, we will add a TextBox (as shown above)
  • Number of Guests we use a ComboBox
  • Number of Nights utilises a scroll bar
  • Finally for Car and Breakfast we will use an Option Buttons or Radio Button
  • Add the controls in the same way that we added the Labels above by selecting them in the Toolbox and drawing them using the cursor

In order for the Option Buttons to work correctly, we need to assign both Car buttons to a Car group and both Breakfast ones to a group called Breakfast:

  • Select each of the four Option Buttons in turn and in their Properties adjust the GroupName as appropriate (so the two Car buttons will be added to a group called Car and the two Breakfast buttons added to Breakfast)

Finally, we need to add two Command Buttons so that we can attach an Add box so that our reservations team can enter the client’s details into Excel and a Close box so that they can close the interface

UserForm with Controls added as well as Text Boxes in Excel 2007 or Excel 2010.
UserForm with Controls added as well as Text Boxes in Excel 2007 or Excel 2010. | Source

To make the code that we are going to write easier to read, we need to ensure that the names of the controls we just added are the same as the text labels:

  • Select the Textbox to the right of the Name label
  • This will open the Properties for this Textbox
  • Rename TextBox1 to Name
  • Finally, we rename CommandButton1 and Command Button2

How to edit the Properties of a Control in Excel 2007 or Excel 2010.
How to edit the Properties of a Control in Excel 2007 or Excel 2010. | Source
  • Repeat this process for all the controls we added.

Conclusion

A UserForm allows you to create a totally configurable User Interface for users of your Excel Workbooks. UserForms:

  • Are intuitive and easy to use
  • Reduce errors and the amount of manual data entry
  • Data can be placed directly into the workbook from the UserForm
  • Error checking can be performed on the input before it is entered into the worksheet
  • Add visual impact and gives your workbooks a professional look

In today’s hub, we created a UserForm which becomes a User Interface for my bespoke Hotel Reservations Interface. We used a number of Controls to complete it, including Command Buttons, Combo Boxes, Text boxes, Spin Bars and finally Option and Radio Buttons. Many thanks for reading; I hope that you found this hub useful and informative.

© 2013 Robbie C Wilson

Comments

    0 of 8192 characters used
    Post Comment

    • profile imageAUTHOR

      Robbie C Wilson 

      3 years ago

      Thanks :)

    • profile image

      iam 

      3 years ago

      nice..work!

    • profile imageAUTHOR

      Robbie C Wilson 

      5 years ago

      Hi Livewirez,

      So glad that you found my hub useful. UserForms allow you to add another dimension to your spreadsheets.

    • livewirez profile image

      Romel Tarroza 

      5 years ago from Pearl of the Orient Sea

      Wow.. nice tutorials. I'm pretty sure this will come in handy.. Thanks for sharing it with us...

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)