Étiquette : userform

The main steps for creating this application are as follows:

First, place the necessary data related to warehouse organization onto Excel worksheets:

• Create six sheets in the workbook: prod_pit, Storeage, shv_izd, aud_vid_texn, mebel, kanc_tov.
• On the Storeage sheet, place the service information required for the program’s operation (the number of product categories, the names of the product categories—food products, sewing items, audio/video equipment, furniture, office supplies—the number of items in stock for each category, the number of warehouse clerks, and their surnames).
• The other five sheets will store the actual data about the goods in the warehouse.
• For each type of product, the following information is stored: product name, weight/quantity, date of receipt into the warehouse, date of release from the warehouse, the warehouse clerk who accepted the goods, the clerk who released the goods, storage cost, and payment confirmation (yes/no).

Next, proceed to developing the main form of the warehouse application:

• Open the VBA code editor.
• Add a user form (via Insert | UserForm). Set the form’s Name property to MainForm.
• Place the necessary controls on the form and set their properties according to Table, so that they appear similar.
• Write code in the form module to handle events for the controls:
  • From the drop-down list, select the product category.
  • The corresponding goods stored on different worksheets will then appear in the list box below.
  • The Receive Goods and Release Goods buttons should open forms that support the respective actions of receiving and releasing goods.

Table. Property values for MainForm and its controls set in the Properties window

Object	Property	Value
Form	Name	MainForm
	Caption	Warehouse
Label	Name	Label1
	Caption	Goods Warehouse
ComboBox	Name	ComboBox1
	Style	fmStyleDropDownList
Button	Name	CommandButton1
	Caption	Receive Goods
Button	Name	CommandButton2
	Caption	Release Goods
ListBox	Name	ListBox1
	BoundColumn	1
	ColumnCount	1
	ColumnHeads	False

The next step is to design the form for receiving goods and the form for releasing goods. The process here is similar to that used for developing the main form:

• configure the appropriate properties for the controls,
• and write the corresponding event-handling code.

Finally, place a button on the Storeage worksheet that opens the main form MainForm.
Also, make sure to enter the necessary code in the worksheet module to handle the button click.

One of the main advantages of MS Excel is its ability to work with homogeneous data arrays, called lists.

Lists in MS Excel are tables where rows contain uniform information.
The rows of the table are called records, and the columns are called fields of the records.

Column fields are assigned unique names, which are placed in the first row of the list—the header row.

In Microsoft Office Excel 2010, for example, the following methods can be used to enter data into a list:

• Using the data form, which is automatically created after defining the list header with the Form command.

NOTE
In earlier versions of MS Excel (including 2003), the data form could be called using the menu command Data | Form.
In MS Excel 2007 and MS Excel 2010, you must add the corresponding Form command to the Quick Access Toolbar or to a ribbon tab by using the Excel Options window and selecting either Customize Ribbon or Quick Access Toolbar.

• Entering data directly into empty rows inserted into the list (in this case, the list range name is automatically redefined).
• Using AutoFill, Series, and the Pick from List command to speed up work.
• Using MS Access forms and then transferring the data into an Excel worksheet.
• Using VBA—where a custom program you write provides a form or dialog box for data entry, which then inserts the values into specified worksheet cells.

The built-in Form tool, whose dialog box is displayed by choosing the Form command, allows filling and editing records in a table list.

A major drawback of this tool is that each field of a record corresponds only to a simple text box.

However, when filling list data, values for some fields are often chosen from a limited set of alternatives (e.g., gender can be male or female, the list of employees in a sales department is limited, etc.).

For such fields, it is more convenient to use list boxes, option buttons, and checkboxes instead of plain text fields.

This approach both speeds up the data entry process and reduces errors (typos) that inevitably occur during manual entry.

Example application
Let us create an application that eliminates the drawbacks of the standard form.

Suppose you are a manager of a travel agency called Through Space and Time! and you are recording information about each client in a data list.

To speed up data entry, you decide to create an application with a dialog box.

To avoid mistakes, repetitive information such as tour destination and type of transport will be selected from lists.
These lists will be filled based on data stored in the worksheets Tour and Transport.

Constructing the application
Create a form and place on it:

• three text boxes,
• five labels,
• a spin button,
• two buttons,
• two combo boxes,
• one frame with two option buttons,
• and another frame with three checkboxes.

Using the Properties window, assign the properties shown in Table.

Table. Property values set in the Properties window

Object	Property	Value
Form	Caption	Registration of tourists of “Through Space and Time!”
Label	Caption	Last Name
TextBox	Name	txtLName
Label	Caption	First Name
TextBox	Name	txtFName
Frame	Caption	Payment and Documents
CheckBox	Caption	Paid
CheckBox	Caption	Photo
CheckBox	Caption	Passport
Label	Caption	Duration (days)
TextBox	Name	txtDays
SpinButton	Name	SpnDays
Frame	Caption	Gender
OptionButton	Name	optMale
OptionButton	Name	optFemale
Label	Caption	Destination (Tour)
ComboBox	Name	cmbTour
Label	Caption	Transport Type
ComboBox	Name	cmbTrans
Button	Name	cmdOK
Button	Name	cmdCancel

Form Module Functionality
In the form module, enter the necessary code, which provides:

• Reading data from the dialog box and entering the record into the first empty row of the data table.
• Entering the duration of the tour either via the keyboard or the spin button, which work synchronously.
• Protecting all data on the worksheets from being modified by the user.

In VBA, in addition to the standard controls already listed, there are a number of additional controls.
These additional controls are independent objects that share the common properties and methods of all controls, while also having their own unique properties and methods.

Adding an additional control
Additional controls can be added to the Toolbox (a new form must be added to the project) as follows:

• Select the command Tools | Additional Controls.
• In the Additional Controls window that appears , check the box next to the control you want to add from the list of Available Controls.
• Click OK.

As a result, the icon for the selected additional control will appear in the Toolbox.

NOTE
When distributing an application that uses an additional control, you must include the corresponding OCX file. The name and location of the OCX file can be found in the Location field of the Additional Controls window.

Removing an additional control
An unnecessary control can be removed from the Toolbox in almost the same way it was added:

• Select the command Tools | Additional Controls.
• In the Additional Controls window that appears, uncheck the box next to the control you want to remove from the list of Available Controls.
• Click OK.

VBA allows you to programmatically display Excel’s built-in dialog boxes, in addition to user-defined dialog boxes. All built-in Excel dialog boxes form the Dialogs collection, where the parameter specifies which dialog box to activate.

Displaying a built-in dialog box on the screen is done using the Show method.

For example, the procedure activates the Open dialog box when a button is clicked.

Displaying the Open dialog box

Private Sub CommandButton1_Click()
    Application.Dialogs(xlDialogOpen).Show
End Sub

Table. Values of Dialogs collection parameter

Parameter Value	Dialog Box
xlDialogFindFile	Open (file search)
xlDialogFileDelete	Delete file
xlDialogGoalSeek	Goal Seek
xlDialogSaveAs	Save As
xlDialogSaveWorkbook	Save
xlDialogPrint	Print
xlDialogPrintPreview	Print Preview

Within the Show method, you can specify parameter values that prefill fields in the dialog box.

For example, when displaying the Goal Seek dialog box, you can specify:

• target_cell (Set Cell field),
• target_value (To Value field),
• variable_cell (By Changing Cell field).

This results in the dialog box appearing with its input fields already filled.

Displaying Goal Seek with predefined values

Private Sub DoGoalSeek()
    Dim fl As Boolean
    fl = Application.Dialogs(xlDialogGoalSeek).Show(Range("A1"), 0, Range("A2"))
    If fl Then
        MsgBox "Solution found"
    Else
        MsgBox "Solution not found"
    End If
End Sub

Open Dialog and the GetOpenFilename Method
There is another way to display the Open dialog box—using the GetOpenFilename method of the Application object.

This method displays the Open dialog box, but it does not open the selected file. Instead, it simply returns the name of the selected file, or the value False if no file was selected.

To open the selected file, you must additionally use the Open method of the Workbooks collection.

Opening a file

Sub OpenDoc()
    Dim FName As Variant
    FName = Application.GetOpenFilename( _
        "Microsoft Excel Workbook (*.xlsx), *.xlx")
    If FName <> False Then
        Workbooks.Open FName
    Else
        MsgBox "No file selected"
    End If
End Sub

The appearance of the dialog box can be controlled by setting parameters of the GetOpenFilename method.

Syntax:

GetOpenFilename(FileFilter, FilterIndex, Title, ButtonText, MultiSelect)

• FileFilter — Optional. A string specifying the filter for displayed files. If omitted, defaults to « All files (*.*), *.* ».
  Example: « Microsoft Excel Workbook (*.xlsx), *.xlsx, Bitmap Files (*.bmp), *.bmp ».
• FilterIndex — Optional. Sets which filter index is used by default.
• Title — Optional. Sets the title of the dialog box.
• ButtonText — Used only in Mac OS X.
• MultiSelect — Optional. Boolean. If True, allows selection of multiple files. In that case, the method returns an array of file names instead of a single string.

Since GetOpenFilename only returns file names (without acting on them), the dialog box can also be used for purposes other than opening files—for example, deleting selected files.

Deleting files

Sub DeleteFile()
    Dim FName As Variant
    FName = Application.GetOpenFilename( _
        FileFilter:="Microsoft Excel Workbook (*.xls), *.xls", _
        MultiSelect:=True, Title:="Delete File")
    If Not IsArray(FName) Then
        MsgBox "No file selected"
        Exit Sub
    End If
    Dim i As Integer
    For i = LBound(FName) To UBound(FName)
        Kill FName(i)
    Next
End Sub

A Simple Graphic File Browser
The GetOpenFilename method returns the name of any type of file, including bitmap files.
At the same time, an Image control can display any bitmap file.

Together, these two facts make it possible to create a simple tool for browsing bitmap images.

So, create a form with a button and an Image control, and in the form module type the necessary code.

The project is ready.

Saving a Document and the GetSaveAsFilename Method
The GetSaveAsFilename method of the Application object displays the Save As dialog box.

Like the GetOpenFilename method, this method does not itself save the file—it only returns the name of the file chosen in the dialog box.
To actually save the file, you must extend the code with a statement that uses either the SaveAs or Save method of the Workbook object.

Syntax

GetSaveAsFilename(InitialFilename, FileFilter, FilterIndex, Title, ButtonText)

• InitialFilename — Optional. Any valid file name that will appear in the File name field. (By default, Excel suggests the name Book.)
• FileFilter — Optional. Sets the filter for the displayed files. If you want a file extension to be automatically added to the file name, this parameter must be used.
• FilterIndex — Optional. Indicates which filter from the File types list will be used by default.
• Title — Optional. Sets the dialog box title.
• ButtonText — Used only in Mac OS X.

Example
The following code can be used when saving a workbook, where by default the suggested file name is Report.

Saving a file

Sub SaveDoc()
    Dim FName As Variant
    FName = Application.GetSaveAsFilename(InitialFilename:="Report", _
        FileFilter:="Microsoft Excel Workbook (*.xlsx), *.xls")
    If FName <> False Then Application.ThisWorkbook.SaveAs FName
End Sub

The MultiPage control allows you to create multi-page dialog boxes. Page titles are displayed on tabs. Navigation between pages is done by clicking a tab.

You can create, rename, delete, or move a page of the MultiPage control manually by right-clicking the tab label and using the context menu.

The MultiPage object contains the Pages collection, which represents all the pages included in this object.
Table lists the main properties of the MultiPage object.

Table. Main properties of MultiPage

Property	Description
Value	Returns or sets the index of the active page. Indexing starts at 0.
MultiRow	A Boolean property. If set to True, tab labels that do not fit in one row are displayed in multiple rows. If set to False, a scrollbar appears when tab labels do not fit in one row, allowing navigation between pages.
SelectedItem	Returns the selected page.

The Pages collection consists of all Page objects, i.e., the pages of a MultiPage control.
The Pages collection has only one property, Count, which returns the number of elements in the collection.
Its methods are listed in Table.

Table. Methods of the Pages collection

Method	Description
Add	Creates a new page.
Clear	Deletes all pages from the collection.
Remove	Deletes a page from the collection.
Item	Returns the page specified by index.

The TabStrip control creates multiple tabs in a dialog box and is functionally equivalent to a MultiPage control.
The TabStrip object contains the Tabs collection, which represents all the tabs.

The TabStrip object and the Tabs collection have the same properties and methods as the MultiPage object and the Pages collection.

Statistics and MultiPage
As an example of an application with multiple pages, let us improve the interface of the project from the earlier section “Determining statistical parameters of a range” .

Create a form with a MultiPage control:

• On the first page (set the Caption property to Calculations), place a RefEdit control, a text box, and a button.
• On the second page (set the Caption property to Parameters), place three checkboxes.

Use the Properties window to set the control properties as shown in Table.

Table. Property values set in the Properties window

Object	Property	Value
TextBox	Name	txtStat
Button	Name	cmdOK
	Caption	OK
CheckBox	Name	chkSum
	Caption	Sum
CheckBox	Name	chkMax
	Caption	Maximum
CheckBox	Name	chkMin
	Caption	Minimum

In the form module, type the required code. The project is ready.

The text box displays multi-line integrated statistics, with the parameters determined by the checkboxes on the Parameters page.

The text box is locked to prevent the user from editing the results.
In addition, the background color of the text box is set equal to the form’s background color.

As a result, the text box visually appears as an indented label within the form.

The RefEdit control is similar to a text box, but it allows you to enter a reference to a range by selecting it directly on the worksheet.
The Value property returns this reference.

TIP
To add the RefEdit control to the Toolbox panel, right-click on the panel and select Additional Controls. In the Additional Controls window that appears, select RefEditCtrl from the list of available controls and click OK.

Determining statistical parameters of a range
As an example of using the RefEdit control, let us construct a simple project that determines some statistical parameters of a range—namely, the maximum, minimum, and the sum of all cell values in that range.

So, create a form with a button and a RefEdit control.
In the form module, type the necessary code.

The project is ready.

To calculate the maximum, minimum, and sum of all values in the range, we use the Max, Min, and Sum properties of the WorksheetFunction object, which correspond to the worksheet functions of the same name.

Solving a system of linear equations
As another example of using the RefEdit control, let us create a project for solving systems of linear equations AX = B.

Here:

• A is an n × n square matrix (the coefficients),
• B is the column of constants,
• X is the column of unknowns.

To solve the system of equations, we use the worksheet functions MInverse() (МОБР in Russian Excel) and MMult() (МУМНОЖ in Russian Excel), which return the inverse of a given matrix and the result of multiplying two matrices, respectively.

Additionally, the MDeterminant() (МОПР in Russian Excel) function is used to check whether the system has a solution by calculating the determinant of the coefficient matrix.

The form window contains a RefEdit control and a button.

The user enters into the RefEdit control a reference to a range of size n × (n+1), where the first n columns contain the coefficient matrix and the last column contains the constants.

Clicking the button triggers the calculation of the solution, which is then output into the range immediately to the right of the selected one.

For example the following system of linear equations is solved:

 

• The coefficient matrix is in range B3:C4.
• The column of constants is in range D3:D4.
• The solution of the system is displayed in range E3:E4.

The Image control allows you to create a simple slide viewer.

As a demonstration, let us create an application for viewing several images.

Create a form with an Image control and a list. In addition, you will need files with corresponding raster images.

In the form module, enter the required code. That’s all — the project is ready.

Modified Chart Wizard
Building a chart with the Chart Wizard normally requires a large amount of preparatory work:

• filling one range with argument values,
• filling another range with function values,
• and then building the chart through a four-step wizard.

Let us create an application that will fill the ranges with argument and function values, build the chart, and display it not only on the worksheet but also in a form.

In this application, the user will only need to enter the interval boundaries and the function itself. The function should be entered not as a worksheet formula but in the usual form, using the symbol « z » as its argument.

The use of « z » as the argument is intended to simplify the code.

Constructing the application
Create a form with three labels, three text boxes, an image, and a button.
Set their Name properties in the Properties window as shown in Table.

Table. Property values of controls set in the Properties window

Control	Name	Description
TextBox	txtBegin	Input of the left boundary of the interval
Label	lblBegin	Label corresponding to txtBegin
TextBox	txtEnd	Input of the right boundary of the interval
Label	lblEnd	Label corresponding to txtEnd
TextBox	txtFun	Input of the function formula. The formula must follow programming rules, with « z » as the argument.
Label	lblFun	Label corresponding to txtFun
Image	imgFun	Displays a raster image from Graph.gif, which contains the graphic representation of the chart.
Button	cmdReady	Triggers the building of the chart.

How the application works
The chart is built from 101 points. First, the step size of the argument is calculated as the difference between the start and end values divided by 100.

The initial argument value is placed in cell A2.
Using the DataSeries method, the argument values are tabulated down the column.

In the function formula, the argument « z » is replaced with a reference to cell A2, and the formula is prefixed with the equals sign « = ».

This formula is entered into cell B2.
With the AutoFill method, the formula is filled down the column to compute the function values for all tabulated arguments.

Based on the argument and function values, a chart is built, and its graphic representation is exported to the file Graph.gif.

The raster image from Graph.gif is then displayed in the Image control imgFun.

Why “z” is used as the argument
Using « z » as the parameter makes the code simpler, since « z » is not part of any built-in worksheet function names.

Thus, it is sufficient to use the single instruction:

f = Replace(LCase(f), « z », « A2 »)

This replaces all occurrences of « z » with the reference to cell A2.

If « x » were used instead, one instruction would not be enough, because « x » appears in built-in function names (for example, Exp()).
In such a case, the instruction would replace not only the argument but also parts of function names, turning Exp() into EA2p(), which would result in an error.

The ComboBox control is used to store a list of values. It combines the functionality of a list and a text box.
Unlike the ListBox, only one item is displayed in the ComboBox at a time. It does not support multiple selection.

The ComboBox control allows the user to enter a value through its text box, similar to the TextBox control.

Its properties, such as ListIndex, ListCount, List, and its methods Clear, RemoveItem, and AddItem, are analogous to those of the ListBox control.

In addition, the ComboBox has several unique properties, listed in Table.

Table. Properties of ComboBox

Property	Description
DropButtonStyle	Sets the appearance of the drop button. Valid constants: fmDropButtonStylePlain (button without symbols), fmDropButtonStyleArrowDisplays (button with arrow), fmDropButtonStyleEllipsis (button with ellipsis), fmDropButtonStyleReduce (button with line).
ListRows	Sets the number of items displayed in the drop-down list.
MatchRequired	Values: True (input must match one of the listed items, i.e. the text box function is disabled) or False (otherwise).
MatchFound	Values: True (an item in the ComboBox matches the input value) or False (otherwise).

ComboBox, alphabetical data entry, and the Collection object
Let us create a simple application where data (e.g., surnames) entered through the ComboBox’s text box is automatically sorted alphabetically.

As soon as data is entered and the key is pressed, the items are displayed in the ComboBox in alphabetical order. When the window is closed, all data from the ComboBox is written into the worksheet cells.

For this application, we will use the Collection object, which is a convenient dynamic storage for other objects.

The Collection object has only one property and three methods:

• Count — returns the number of elements.
• Item — returns an element.
• Add — adds a new element.
• Remove — deletes an element.

The code provided serves both as a good example of working with the Collection object (insertion of new items is determined using a binary search algorithm) and as a demonstration of entering data into the ComboBox from its text box.

So, create a form, place a ComboBox on it, and in the form module type the required code.

Adding and deleting data in a ComboBox
Now let us consider an application where new items (different from those already present in the list) can be added to the ComboBox through its text box.

Thus, entering a duplicate item (an item already in the list) is not allowed.
This makes the data entry approach different from the one described in the previous section.

Additionally, items can be deleted from the ComboBox, and the entire list can be cleared.

Initially, the list is populated with data from a cell range. If a new item is entered in the ComboBox text box, it is also added to the underlying range that fills the ComboBox. When an item is deleted from the list, it is also removed from the range.

In both cases, the size of the range changes, since rows are added or removed. Therefore, the range used to populate the ComboBox must be redefined constantly.

Constructing the application

Create a form with a ComboBox and three buttons. Set their property values in the Properties window as shown in Table.

Table. Property values set in the Properties window

Control	Property (Name)
ComboBox	cboNames
Button	cmdAdd
Button	cmdDelete
Button	cmdClear

The list will be filled with data (e.g., surnames) from a column starting at cell A1.

• Clicking the Add button adds a new element from the ComboBox text box to the list.
• Clicking the Delete button removes the selected item from the list.
• Clicking the Clear button clears the entire list.

The ListBox control is used to store a list of values. In the list, the user can select one or more values, which are then used in the program.

Note that at design time, a list visually resembles a text box. Typically, selecting an item from the list is done by clicking on it. A double-click on an item, however, is often used to perform certain actions in the program associated with that item.

Item-by-item population of a list
A list can be populated item by item using the AddItem method.

As an example, let us create a simple project), which contains a form with a list. City names are added to the list. Selecting an item from the list does nothing, but double-clicking on an item outputs it into the next free cell of the first column of the active worksheet.

The selected value from the list is returned by the Text property. The number of filled cells in the column is returned by the CountA property of the WorksheetFunction object.

Populating a list from an array and selecting an operation
A list can be populated item by item or in one operation. To do this, assign the List property of the ListBox to a variable that contains an array of values.

As an example, let us once again return to the project from the section “Addition of two numbers” earlier in this chapter.
This time, instead of calculating the sum of two numbers, we will perform one of four arithmetic operations listed in the list.

So, create a form that contains three labels, three text boxes, a button, and a list.
For all controls except the list, assign property values using the Properties window as described in the section “Addition of two numbers”.
For the list, assign the Name property the value lstOp.

In the form module, type the required code.

An item in the list is identified by its index, which is returned by the ListIndex property. Indexing starts from 0.
In this code, the selected item (i.e., the operation) is displayed in the form’s title bar. This is achieved by handling the Click event of the list.

Populating a list from a range
The RowSource property allows a list to be populated from a range. The value of this property is a string containing the name of the range.

Let us demonstrate the use of this property with an example, considering both the case when the range is known in advance and the case when only a single cell of the range is known.

We will create a simple project for recording business trips of employees of a certain company. The workbook contains three worksheets: Cities, Employees, and Trips.

• On the Cities sheet, the range A1:A5 contains the list of cities where employees are scheduled to travel.
• On the Employees sheet, column A starting from cell A1 contains the list of employee names. The size of this list is not fixed and may change depending on the user.
• On the Trips sheet, two columns display the list of employee trips by cities.

The list on the Trips sheet is populated using the Populate list from array window.

This window contains two lists (with employee names and city names) and a button. The lists are populated from the ranges of the corresponding worksheets.

Clicking the OK button enters the selected data into the next free row of the trip list on the Trips sheet.

Create a form containing two lists and a button, and use the Properties window to assign their values as shown in Table.

Table. Property values set in the Properties window

Object	Property	Value
ListBox	Name	lstName
ListBox	Name	lstCity
Button	Name	cmdOK
	Caption	OK

In the form module, type the required code.

When the form initializes, the lists are populated.
The city list is filled by explicitly specifying the range address.

Before populating the employee list—since its size may vary—the program first identifies the range, then determines its address, and only then fills the list.

When the OK button is clicked, the program first checks whether data has been selected from the lists, then determines the first free row in the list on the Trips sheet, and outputs the selected data from the form into that row.

Selecting multiple items from a list
In a list, it is possible to select either a single item or multiple items. The MultiSelect property defines the mode in which such selection is allowed.

The valid values of the MultiSelect property are the following constants:

• fmMultiSelectSingle — only one item can be selected.
• fmMultiSelectMulti — clicking an item or pressing the key selects or deselects the item.
• fmMultiSelectExtended — clicking an item while holding the key selects or deselects the item. Clicking an item while holding the key selects a range of items from the previously selected one to the current one.

The Selected property is used to identify the selected items. If its value is True, the item is selected; if False, it is not selected. The List property returns the item at the specified index, remembering that indexing starts at 0. The total number of items in the list is returned by the ListCount property.

As a demonstration, let us create a project in which multiple items can be selected from a list. Create a form with a list and a button, and using the Properties window, set their values as shown in Table.

Table. Property values set in the Properties window

Object	Property	Value
ListBox	Name	lstNum
Button	Name	cmdOK
	Caption	OK

When the form initializes, the list is populated and the mode allowing multiple selection is set. Clicking the OK button then sequentially reads the selected items and composes an information string, which is displayed in a message box.

Coordinated operation of two lists
It is often necessary to coordinate the operation of two lists. For example, a publishing house may cooperate with certain stores in different cities. One list displays a list of cities. When an item is selected from this list, the second list displays the list of stores.

How can this be done? Very simply. Array elements can have the Variant type, and a Variant variable can hold anything, including another array. Therefore, in VBA it is possible to create an array of arrays

Multi-column lists
Lists can be not only single-column but also multi-column. To create a multi-column list, set the ColumnCount property to the desired number of columns.

In addition, the ColumnWidths property can be used to set the width of each column in the list.

ColumnWidths = String

Here, String is a text string consisting of numbers representing the widths of the corresponding columns, separated by semicolons. For example, the string « 90;80 » means that 90 points are allocated to the first column and 80 to the second.

Access to list elements is provided by the List property, where the first parameter is the row index and the second is the column index. Both rows and columns are indexed starting at 0:

List(row, column)

As an example, let us create a demo project in which a two-column list displays a given number of random numbers. Create a form with a list, a text box, and a button, and using the Properties window, set their values as shown in Table.

When the form initializes, the list is set to two columns, with 30 points allocated to the first column and 50 to the second. When the OK button is clicked, the list is first cleared with the Clear method and then populated with the specified number of random numbers and their indexes. Selecting an item in the list displays a message box with information about the chosen value.

Table. Property values set in the Properties window

Object	Property	Value
ListBox	Name	lstRnd
TextBox	Name	txtNum
Button	Name	cmdOK
	Caption	OK

Populating a multi-column list from a range and calculating the average value of selected numbers
Like a single-column list, a multi-column list can also be populated from a data range.

As an example, let us build an application that calculates the average grade of a selected group of students from a list. The list is populated from a range with its top-left corner at cell A1. The first column contains student names, and the second contains their grades.

Create a form with a label, a text box, a list, and a button. In the form module, enter the necessary code. At form initialization, the list is populated and the Caption properties of the controls and form are set.

When the OK button is pressed, it first checks whether any items have been selected. This is necessary because the average grade equals the sum of the grades divided by their count. If no items are selected, the formula would cause a division-by-zero error.

Hiding data in a multi-column list
If any component in the ColumnWidths property is set to zero, the corresponding column is hidden. This can be used to hide auxiliary information.

For example, let us create a demo project called “Human Resources.” In it, a list is filled from range A2:C9 of the Employees worksheet with information about company employees: surname, position, and years of service (Fig. 4.39). Although all this data is loaded into the list, only the surnames are displayed.

When an item is selected, a message box displays the employee’s surname. By setting the required checkboxes (Position and Experience), additional information about the employee can be shown.

To implement this project, create a workbook with a worksheet named Employees and enter employee data into range A2:C9. Create a form with a list and two checkboxes. In the form module, enter the code.

At form initialization, the list is populated and the Caption values of controls and form are set. When an item is selected, a message string is generated depending on the state of the checkboxes and displayed in a message box.

The first column element is returned by the Text property, while the second and third columns are returned by the List property.

Changing the state of a checkbox also triggers the list’s Click event procedure, provided there are selected items. This is checked by the value returned by ListIndex. If no item is selected, ListIndex returns –1.

Displaying selected values from a multi-column list using the Text and Value properties
By default, the Text property returns the selected element from the first column. However, this can be changed using the TextColumn property, which specifies the column whose selected element is returned by Text.

In addition to Text, the list also has a Value property that works similarly. The column from which it returns a value is defined by the BoundColumn property. Note that in both TextColumn and BoundColumn, column numbering starts at 1.

The following code is a modification of the previous project, where now the values from the second and third columns are read using the Text and Value properties. Since the list contains three columns (with the last two hidden), the value from the first column is read using the List property.

Displaying selected values from a multi-column list using the Text and Value properties

Private Sub UserForm_Initialize()
    ListBox1.ColumnCount = 3
    ListBox1.ColumnWidths = "80;0;0"
    Me.Caption = "Human Resources"
    ListBox1.RowSource = "Employees!A2:C9"
    CheckBox1.Caption = "Position"
    CheckBox2.Caption = "Experience"
    ListBox1.TextColumn = 2
    ListBox1.BoundColumn = 3
End Sub

Private Sub ListBox1_Click()
    Dim msg As String
    msg = "Last Name: " & ListBox1.List(ListBox1.ListIndex, 0) & vbCr
    If CheckBox1.Value Then
        msg = msg & "Position: " & ListBox1.Text & vbCr
    End If
    If CheckBox2.Value Then
        msg = msg & "Expérience: " & ListBox1.Value & vbCr
    End If
    MsgBox msg
End Sub

Dragging items from one list to another
The DataObject object can serve as a data carrier when programming a drag operation. Its methods allow you to control the drag process from start to finish.

Table. Methods of the DataObject object

Method	Description
Clear	Removes the transferred data from the object.
GetFormat	Returns 1 if the transferred data is in text format.
GetFromClipboard	Copies the contents of the clipboard into the object.
GetText	Returns the string stored in the object.
PutInClipboard	Moves data from the object into the clipboard.
SetText	Copies a string into the object.
StartDrag	Initializes the drag operation. The allowed return constants are: fmDropEffectNone, fmDropEffectCopy, fmDropEffectMove, fmDropEffectCopyOrMove.

When coding a drag operation, you must handle two events:

• BeforeDragOver — triggered during the drag operation.
• BeforeDropOrPaste — triggered immediately before the object is dropped.

To demonstrate how a drag operation can be programmed, create a form, place two lists on it, and enter the corresponding codeD). The project is ready.