User Form Toolbox Controls

Table 4-1. UserForm Toolbox Objects

Toolbox Button Command_Description_

Resizes or moves a control on a form.

Holds text that is not editable except through code. Holds text that users can enter or modify.

Continued abl

Select Objects

Label

TextBox

Table 4-1. Continued

Toolbox Button

Command

Description

ComboBox

ListBox

CheckBox

OptionButton

ToggleButton Frame

CommandButton TabStrip MultiPage ScrollBar

SpinButton

Image

RefEdit

A combination of a list box and a text box. Users can choose an item from a list or enter a value in the text box.

Displays a list of items from which users can choose.

Indicates a true or false value.

Presents multiple choices, of which only one can be selected.

A button that toggles off and on.

A grouping for controls such as option buttons or check boxes. Users can only select one of a group of controls placed inside a Frame control.

A button the user can click to perform an action.

Multiple pages in the same form area.

Multiple screens of information.

Provides quick navigation through a long list of items. It is also useful for indicating the current position on a scale, or as an input device or indicator of speed or quantity.

Increments or decrements a numeric value.

Presents an image from a bitmap, icon, or metafile.

Simulates the behavior of the reference edit boxes such as the Range selector in the Print Area section of the Page Setup dialog box.

On UserForml, add the controls and enter the property settings listed in Table 4-2.

Table 4-2. UserForm1 Settings and Controls

Item/Property

UserForm

Caption: Label

Caption:

Label

Name:

Caption:

Label

Caption:

Value

Customer Survey Form ID

lbllD Labell

State

Item/Property

Value

TextBox

Name:

txtState

Label

Caption:

Phone Number

TextBox

Name:

txtPhone

CheckBox

Name:

chkHeard

Caption:

Customer Has Heard of Product

CheckBox

Name:

chkInterested

Caption:

Customer Is Interested in Product

CheckBox

Name:

chkFollowup

Caption:

Followup Required

CommandButton

Name:

cmdSave

Cancel:

False

Caption:

Save

Default:

True

CommandButton

Name:

cmdNew

Cancel:

False

Caption:

New

Default:

False

CommandButton

Name:

cmdCancel

Cancel:

True

Caption:

Cancel

Default:

False

The finished form should look like Figure 4-4.

Figure 4-4. UserForm with controls placed

As you can see, we're designing a very simple data collection tool. We're going to write a record to the database that is stored on Sheet1 in the UserForm workbook, and we want to do some validation of the data before we save.

Before we begin, we need to think about a couple of functions we might need and how to approach our code design. First, our form needs to know which worksheet to save the data to (in this case, Sheet1 contains our database). It also needs to know the next available ID number and the location of the next available row to place the data when we save the data.

A function that can tell us where the next available row in a worksheet is might be useful in another project as well. Remember the cExcelUtils class we started in Chapter 2? Let's put our function in that class and export it so we can reuse it in other projects.

Open the last project you worked on in Chapter 2, DataAccessSample06.xlsm, and then open the VBE. In the Project Explorer, right-click the cExcelUtils class icon and choose Export File from the shortcut menu, as shown in Figure 4-5.

B S Class Modules i © cData

M VRAProiect

Properties - cExcel?

cExcelSetup ClassM Alphabetic | Categori

Instancing : 1 - Private ilin View Code [=U View Object

VBAProject Properties... Insert

Import File... Export File... Remove cExcelSetup... i-^j Print... v Dockable ™J Hide

Figure 4-5. Exporting a module

Choose your location and save the *.cls file. Once that's done, you can close the DataAccessSample06.xlsm workbook. Right-click anywhere in the Project Explorer in the UserForm.xlsm project, and choose Import File, as shown in Figure 4-6. Navigate to where youjust saved the cExcelUtils.cls file and choose the Open command.

- ft Modules Jroperties - UserFori

UserForml UserForm Alphabetic | Catégorie

- ft Forms

3ackColor

EorderColor

EorderStyle

Caption

Cycle

DrawEuffer

Enabled

Font

VEAProject Properties... Insert

Import File... Export File... Remove UserForml... Print... ✓ Dockable Hide

Figure 4-6. Importing a module

The cExcelUtils class is now a part of your project. Open the cExcelUtils class in the VBE and add the following method. The FindEmptyRow function returns a Long Integer containing the row number of the next available row on a worksheet.

Function FindEmptyRow(ws As Worksheet) As Long Dim lngReturn As Long lngReturn = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row FindEmptyRow = lngReturn End Function

This simple bit of code uses the Range object's End property to find the last cell in the region and offsets it by 1. We're passing in a worksheet as an argument so the function will return the next open row in the passed worksheet.

0 0

Post a comment