Forms (Userforms) in VBA for Excel

    

613-749-4695 / 514-257-0734
[email protected]

Home Page

Tips and Ideas on Excel
Table of Contents on Excel

Tips and Ideas on Macros
Table of Contents on VBA

Quick Links

 

VBA Chapter 23A of 35: Userforms Properties and VBA Code

The UserForm Properties

As you have seen in section 3 on the Visual Basic Editor you double click on the userform's name in the Project window and its properties appear in the Properties window:

VBA for Excel userforms properties

In the Properties window of the VBE you MUST  change the name "(Name)" of the form, its caption (the name in the blue band at the top of the UserForm) and you can also modify the default setting of any of the 32 other properties of the form.

When you name a form always use the prefix "frm" like in "frmDatabase" and be as descriptive as you can be so that your code will be easy to read. Always use one or more upper case letters in the name. When you write "frmdatabase.show" in lower case letters Excel will capitalize some letters "frmDatabase.Show" letting you know that the name is spelled correctly.

The caption is what your users will se at the top of the userform. Be as informative as possible and complete the information with a label if necessary.

VBA for Excel asword userform

Else than the Name and Caption there are just a few properties that you might want to modify. You can select a different color for the background with the property "BackColor" .

By default the userform appears in the center of the screen. If you want it to show somewhere else set the "Start" property to "0-Manual" and use the "Top" and "Left" properties to set a new position.

The Code

Opening and Closing the Userform

The first thing to do is to create code to call your userform. Here is a basic line of code  doing so:
frmCity.Show
See Chapter 9 on Events to learn how to assign macros to a text box or other button that you place on the sheet.

The line of code to close the userform is:
frmCity.Hide
or Me.Hide "Me" being the general name of the active form

The "Hide" sentence is usually part of the code of a command button on the form. A user clicks on a "GO" , "SEND, "SUBMIT" or  "CANCEL" button and part of what must happen is that the userform disappears from the screen.

 

Next Chapter: Controls on Userforms in VBA for Excel

VBA Table of Contents
or use the quick links below

   

Discover Even More in 50 Excel spreadsheets

 

    

613-749-4695 (Peter)
[email protected]

Home Page

Tips and Ideas on Excel
Table of Contents on Excel

Tips and Ideas on Macros
Table of Contents on VBA

Quick Links

Forms (Userforms) in VBA for Excel