Adding Controls to User Forms

Adding Controls to User Forms

Adding Controls • A user form isn’t much use without some controls • We’re going to add controls and write code for them • Note that the code to OPEN the form belongs to the workbook project • The code for controls on the form, though, belongs to the form, and is on a separate code window

Mac vs Windows • The following slides show the Windows version • There are videos of doing both versions, and there are a few slides at the end of this presentation that show how things look on the Mac • Mac users should read through the whole presentation as not everything is duplicated

Double click on the form name to get back to its code Click here

Ready to add controls… This is the design view of the form, where you can add controls and set their properties.

Useful Controls (1) • Listboxes: for printing data for the user to see. Also good for giving a list of choices to pick from. • Command Button: clicking the button creates an event we can write a program for • Labels: used to put useful information and instructions on the form • Check boxes: used to offer options where the user can pick any number from none to all

Useful Controls (2) • Option buttons: used to offer options where exactly one should be chosen • Frames: used to group controls like option buttons; needed if there is more than one set of options • Textboxes: a place for the user to type input • There are some others which we won’t use this term

Events for Controls • Most things you do with a control, like checking a check box, typing in a text box, or clicking a command button, are events in VBA • You can write code to handle any control event • If you don’t write code for an event, then nothing happens • We will typically use the command button control to trigger execution of our program

Finding the UserForm Code Page If you click on the Window item, you can move between the code page and design page for the form and the code page for the workbook.

The Form Code Page This looks a lot like the window for the workbook code, so be careful you use the right window! I just typed Option Explicit at the top.

Adding Four Controls A label. I changed the caption, font, and size properties A text box. I changed the name to txtName. A label. I changed the name to lblHello, the font to a large size, and the Visible property to False. I changed the caption property and the name to btnPrint

Names for Controls • Controls that are referred to in the code should have meaningful names. • Start with a three letter prefix that identifies the control: btn for button, lbl for label, txt for text box. • You can leave the names unchanged on controls that are not referred to, like most labels.

Writing the Code • To get started, double click on the control you want to write an event routine for. (This only works if you have already opened the code window for your form.) • VBA will guess the name of the routine and put the procedure header on the code page, all set for you to start coding.

Here’s what I got by double clicking on btnPrint

My code changes the properties of the label lblHello: Option Explicit '*************************************************************** 'Demonstrate the use of controls on a user form '*************************************************************** Private Sub btnPrint_Click() Dim name As String '*** read the name from the text box name = txtName.Text '*** create the caption and show the label lblHello.Caption = "Hello, " & name & "!" lblHello.Visible = True End Sub

Push F5 to run the code, or use the Run menu

After I typed my name and pushed the button, here’s what I got!

Try it Yourself! • You can download the example program and try using it • Try making some changes in the properties, fonts, colors, etc. of the various controls, and in the layout of the user interface • Also try adding a button that does something to the text in the large label

Mac version

Adding Controls • A blank user form isn’t much use! • Let’s add a few controls. • When using controls that our program refers to, we give them meaningful names. • We have a standard way to construct names. Form names start with frm; buttons with btn; text boxes with txt; labels with lbl. We’ll see others as we go along.

Watch the Video • There’s a video that shows the construction of this user form application • The following slides show some of the major highlights

Add controls I’ve added two labels, a text box, and a command button, using the palette at right to choose my controls, and the properties window to set their properties.

The large label I changed the font size on the large label to 16, made it a bold font, and changed the name to lblHello. I set its Visible property to False, so you don’t see it when you start the program.

The command button I changed the name of the button to btnClick and the caption to read “Now Click This Button”. I changed the font size to 12, as well.

The Code Option Explicit '****************************************************** ' Read name from text box and print on label '****************************************************** Private Sub btnClick_Click() Dim name As String name = txtName.Text lblHello.Caption = "Hello, " & name & "!" lblHello.Visible = True ‘important so we see the message! End Sub

Try it Yourself! • You can download the example program and try using it • Try making some changes in the properties, fonts, colors, etc. of the various controls, and in the layout of the user interface • Also try adding a button that does something to the text in the large label

Adding Controls to User Forms