Posted by
Andrew Gould
on 31 March 2016
Part 1 of this Excel VBA User Forms Tutorial explains how to draw forms and controls. You'll learn how to arrange objects neatly and see a variety of formatting options for basic text boxes, labels and command buttons.
Posted by
Andrew Gould
on 31 March 2016
Once you've drawn a form you'll need some way to display it to your users. This part of the series explains how to run a form using buttons on a worksheet and shows you how to navigate through a form by setting the tab order of controls and creating accelerator keys.
Posted by
Andrew Gould
on 31 March 2016
After you've made your form look beautiful you'll want it to actually do something useful! This part of the series explains how to add code to the events of the form and its controls to make it work. You'll learn how to refer to the form and objects on it, how to change their properties and how to make the form respond to the user's actions.
Posted by
Andrew Gould
on 31 March 2016
Validation is all about making sure a user has entered sensible values in a control. This video shows you how to use the before- and afterupdate events of a textbox to prevent a user continuing when they've entered invalid data. You'll learn how to check if a value is a number or a date and how to modify the properties of controls at runtime to indicate that there's a problem.
Posted by
Andrew Gould
on 31 March 2016
Another aspect of validation is checking that every control on a form has been filled in. This video explains how to loop over the controls collection to check if every textbox has been completed.
Posted by
Andrew Gould
on 31 March 2016
A ComboBox allows you to create a drop down list on a form, which means that you can provide users with a limited list of choices. This video explains how comboboxes work!
Posted by
Andrew Gould
on 31 March 2016
A ListBox is similar to a ComboBox but with the added advantage that you can select multiple items! This video explains how to work with listboxes on your form.
Posted by
Andrew Gould
on 31 March 2016
Option Buttons are useful for providing a set of mutually exclusive options on a form. This video explains how to create groups of option buttons and test which option has been selected.
Posted by
Andrew Gould
on 31 March 2016
Spin Buttons, or Spinners, provide a convenient way to increase and decrease a numeric value. This video explains how spin buttons work, including how to synchronise a spin button with a textbox.
Posted by
Andrew Gould
on 31 March 2016
A Scroll Bar is like the big brother of a Spin Button, allowing you to set a numeric value by clicking and dragging a bar. This video shows you how to use a scroll bar to control both a numeric value and a date.
Posted by
Andrew Gould
on 31 March 2016
A Check Box is a simple control which allows the user to indicate an option by checking a box! This video explains how to use check boxes, including how to use a triple-state check box to determine if the user has actually selected an option.
Posted by
Andrew Gould
on 31 March 2016
A Toggle Button is functionally identical to a Check Box, although cosmetically it's quite different. This short video provides a quick overview of how toggle buttons work.
Posted by
Andrew Gould
on 31 March 2016
Image Controls allow you to display pictures on your forms. This video shows you how to allow a user to select the image that's displayed, including how to use a File Picker dialog box to control which types of image files they can select.
Posted by
Andrew Gould
on 31 March 2016
A Multi Page control is perfect for organising lots of controls into discrete categories to make your forms neat and tidy. This video explains how to work with multi page controls, including how to loop over the pages collection for quickly validating your form.
Posted by
Andrew Gould
on 31 March 2016
A Tab Strip looks similar to a Multi Page control although it behaves very differently. This video explains the slightly odd way in which tab strips work, including how to loop over the tabs collection.
Posted by
Andrew Gould
on 04 April 2016
Playing videos in a VBA user form is perhaps a little frivolous but it makes for a fun video! You'll learn how to add extra controls to the toolbox, how to use the Shockwave Flash Object and a little about how to use VBA's string functions to manipulate YouTube's video URLs.
Posted by
Andrew Gould
on 18 April 2016
User forms aren't just for data entry; you can use them to edit and delete data too! This video explains how to create a form which allows the user to browse a set of records held in a worksheet and to edit and delete that data using the form. Along the way, you'll learn about a couple of advanced programming techniques such as declaring Enumerations and Property statements.
Posted by
Andrew Gould
on 26 October 2016
Entering dates into a text box is awkward, difficult to validate and dull. Selecting dates from a dedicated Date Time Picker or Calendar solves all of these problems at once! This video explains how to install and register the Microsoft Date and Time Picker Control and the Microsoft Month View Control and explains how to add them to your User Form Control Toolbox. The video also briefly explains how to use these controls in your forms.
Posted by
Andrew Gould
on 31 October 2016
The RefEdit control provides you with a way to select cells from the comfort of a user form in Excel. This video explains how to make the RefEdit control available, how to use it to select cells and how to use the value it returns to manipulate the cells you've selected. The video also inculdes a section on a few potential issues with the RefEdit control and shows a couple of alternative techniques using the Application.InputBox method.
Posted by
Andrew Gould
on 06 February 2017
This video explains the basics of using progress bar controls on your VBA user forms. You'll see how to add the control to the toolbox and how to draw and edit progress bars on a form. Most importantly, you'll learn how to update the value of a progress bar at run time so that it indicates progress through a procedure.
Posted by
Andrew Gould
on 03 April 2021
This video explains how to use a single ActiveX spin button control in Excel to control different cells in a worksheet. You'll learn how to set properties of a spin button control using the Properties window and using VBA code. You'll also learn how to use the Worksheet Selection Change event to trigger code when a user clicks on a cell. Finally, you'll see how to use some basic If statements to check if a cell value is number and if the number falls within a range.
Posted by
Andrew Gould
on 30 September 2021
This video explains how to add a select all option to a listbox on a user form. Most importantly, you'll see how to avoid triggering an endless cascade of events!
