This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Writing VBA macros to work with user forms Part one of a three-part series of blogs
The previous part of this three-part series showed how to draw user forms; this part shows how to write macros to get them to work (the final part shows how to use some of the more advanced controls, such as combo boxes and MultiPages). Coding Forms - the Basics (this blog) Form and Control Events Validating Form Data This blog is part of our Excel VBA tutorial blog. If you want classroom training (for small groups of up to 6 people), take a look at our courses in VBA.

Writing VBA macros to work with user forms

Part one of a three-part series of blogs

The previous part of this three-part series showed how to draw user forms; this part shows how to write macros to get them to work (the final part shows how to use some of the more advanced controls, such as combo boxes and MultiPages).

Coding Forms - the Basics (this blog)
Form and Control Events
Validating Form Data

This blog is part of our Excel VBA tutorial blog. If you want classroom training (for small groups of up to 6 people), take a look at our courses in VBA.

Posted by Andy Brown on 27 February 2012

Coding Forms - the Basics

It's all very well drawing a pretty user form, but you then need to get it to interact with Excel! That's what this blog is all about. You can also read:

How to draw user forms
How to create and code some advanced controls

Running a User Form from the Code Window

Seeing what a form will look like is easy: just click on the form background to activate it first, then press the F5 key or click on the Run tool:

Here we're about to click on the green triangular play tool to see what the form looks like.

Unless you've attached code to any of the buttons, the only way to close down your form will then be to click on the cross at the top right:

In an ideal world the Cancel button would work too!

Running a Form from a Macro

A more likely scenario is that you'd want a user to run your form when they open a workbook or click on a button:

You can attach a macro to this button so that when a user clicks on it, your form appears.

To reach this situation you need to write a macro in a separate module. Here's how this could read:

Option Explicit

'the macro can be called anything you like

Sub ShowForm()

'this command would load the form, but not show it on screen

Load frmDrink

'this command would load and show the form

frmDrink.Show

End Sub

This macro has to be in a separate module because the ShowForm procedure above has to be publicly exposed. Routines that you write within a form are private to that form, and nothing else can see them.

The Difference between Show/Hide and Load/Unload

The order of events for a form is as follows:

The order of events for a form is shown here.

The main reason that the order of events above is important is that the Initialize event for a form will only run when it loads: so if you hide and then redisplay a user form, any code attached to the Initialize event won't run.

Now we've seen how to run a form, it's time to look at getting the buttons to work - and for that we need to look at form events.

Parts of this blog
Coding Forms - the Basics (this blog) Form and Control Events Validating Form Data

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC