The Power of Sub Classes in VBA with Kent Gorrell

Please join us for our Monthly Chapter Presentation January 4th, 2024, at 6:30PM Pacific (UTC -8)

Kent will draw on his decades of experience providing solutions for his clients to share his insights into the Power of Sub Classes.

Here’s an outline of what you can expect, in an Overview, a longer version and a detailed version. (Something for every level of interest.)

Overview: Introduction to Form and Control Sub Classes

Examples of how to create a consistent User Experience with very little code.

These examples should get you thinking about what else you could do in your applications.

Longer Version: Exploration of the Form and Control Sub Classes

Implementing Form and Control Sub Classing examples to make your applications consistent with very little code. You’ll learn about the following.

  • Raising Events to enable and disable buttons like Save and Undo when you edit or save a record
  • Using a Button class to do the saving and undoing with no code behind your button or form
  • Text Box Class to highlight the current control without using Format Conditions
  • Change the on focus background color for you entire application in just one place
  • Use a different on focus background color for locked or unlocked controls
  • Prevent illegal characters like a-z in date controls
  • And plenty more

And all with as little as 3 lines of code behind each form

The sample application is available for download;  it’s yours to download and keep.

Extended Version: Step by Step Through the Presentation


  • In this presentation we are going to look at what a form sub class can do
    • How it works
    • How we implement it
    • What we can do with Control sub classes
    • How they work
    • How to implement them

 The Form Class

A simple example of what a class can do to enhance your forms making forms faster to code and more consistent.

  • Code Stepthrough
    • Instantiate the class and step through the init method of the class
    • Establish [Event Procedure] for events
    • Display label caption for Version Number

Simple Form class event examples

  • Dirty, Undo, AfterUpdate events to enable, disable buttons for Save and UndoHere we see the buttons on the form enable and disable using the form class with no code in events behind the form

Classes for Controls

  • Adding Control Classes to the collection in the Form Class
  • Examples of what Control Classes can do for text boxes and buttons to make your application more consistent with very little coding
  • Add Controls to collection in form class to instantiate themstep through the control class init method

Control event examples include:

Command Button

  • Click event for buttons – Save, Undo, CloseUsing Code in the class or using a procedure in the form that can also be called from the ribbon.

Text Box

  • On Focus background color for text boxeschange the on focus back color for the entire application with just one constantuse a different back color for the text box depending on if it is locked or unlocked

Advanced Control Events

  • Identifying the control source data type and size in the Init method of a text box
  • Validation for Text Length
  • Nullify non numeric characters for Numbers and dates on key down event
  • Add or subtract a date using + and – in the key down event


  • How we easily added a declaration of the form class object to a form
  • Then instantiated it
  • All of the wonderful things it does to make all the forms and controls work consistently throughout your application while removing the repetitive code behind the form that we no longer need

Access Pacific holds FREE Q&A webinars the First Thursday of every month, from 6:30 pm to 7:30 pm Pacific.

Add our monthly meeting to your Outlook calendar.


Please prepare ahead of time by downloading Zoom, if you don’t already have it.

Get Zoom

To join our meetings, please use the following link and phone number or use your computer’s speakers with audio:

When it’s time, join the meeting from here:

Join Zoom Meeting

If you are asked:

Meeting ID: 861 2395 1916
Passcode: 123456

Dial by your location
+1 253 215 8782 US (Tacoma)
+1 346 248 7799 US (Houston)
+1 669 900 9128 US (San Jose)
+1 301 715 8592 US (Washington DC)
+1 312 626 6799 US (Chicago)
+1 646 558 8656 US (New York)
Meeting ID: 861 2395 1916
Passcode: 123456
Find your local number