|
|
This lesson covers ways to ensure user provides valid data, whether through selection from presented valid choices, or through validation of typed data.
The programmer’s best option is to present a list of valid choices. The second option is to give the user a procedure which will result in a valid choice. Only when neither of those options is available should the user be allowed free-form data entry. VBA provides a number of functions to check data, and also, some restrictions on the data may be known.
Listing Valid Choices
There are two possibilities for listing valid choices. One is to have the system provide the choices, but there are only a few cases where this is possible: file names, folder names, numbers, dates, times, cell addresses. Controls for selecting the last three will be presented in Lesson 10.
The second possibility for listing valid choices is to use ListBox or ComboBox to pick from a list. This depends, of course, on either the programmer or the user ensuring that the list only contains valid entries.
Selecting Files from GetOpenFilename
A folder or filename will be known to be valid if the system lists. The Application object has a builtin dialog box called GetOpenFilename which is identical in appearance and function to the standard Open dialog box used by many Microsoft applications. It allows browsing through folders, displaying files of specified types, and selecting of one or more files. In its simplest form:
Dim fName As String
fName$ = Application.GetOpenFilename("Excel Files (*.xls), *.xls ")
If fName$ = "False" Then
Exit Sub ' Cancelled
else
MsgBox fName$
End If
The string returned is the full path of the selected file, suitable use with the Workbook.Open method. There are options for listing different file types, for choosing which file type to show first, and for selecting multiple files. In UserForm Control Demos.xls, click “Select Filenames(s)” on sheet Lesson 9 for examples of complete implementation.
Parsing Full Path Names
The FileScriptingObject provides a way to parse a full path name into its components, particularly file name, parent folder name, and extension. It is used in UserForm_Lesson09, and an example parseFullPath procedure is included in the UserFormLessonsUtilities module.
Selecting a Number from SpinButton
An exercise in Lesson 3 stepped through creating and setting up a SpinButton. The parameters can be set by code as well:
With SpinButton1
.Min = 1
.Max = 100
.Value = 50
.SmallChange = 10
.ControlSource =
"C10"
End With
A SpinButton will always return an integer in the specified range. Though SpinButton exercises typically use the integer value directly, the example in Lesson 3 was used to pick from a list. The integer output can also be scaled when it is used:
TextBox1.Value = SpinButton1.Value / 100
Selecting with CheckBoxes and OptionButtons
If an invalid selection is made either with CheckBoxes or OptionButtons, then it is clearly a problem with the implementation and is up to the maintainer of the file to correct it.
ComboBox and ListBox
Everything in the lists of ComboBox and ListBox should have been checked before it was added to the list, and shouldn’t need checking again.
Remember when using the Value property for these two controls, that it is for the BoundColumn. These two code statements return the same information
.Value ' returns the BoundColumn value
.List(.ListIndex, .BoundColumn - 1) ' list indices start at 0
The indices for the List property array begin at 0, and BoundColumn.begins at 1.
Adding Entries to Lists
A common use of a UserForm is to update database records or add new ones. Such a UserForm would have Update, Add, and Cancel/Close buttons. A possibility with this combination of buttons is that a record could be altered and then saved as a new record. This would minimize the fields that might have validation errors. Whether totally new or a modification of an existing record, clicking the Add button will add the record to the database.
If it is a single field list, then AddItem can be used, but then the addition is in place only until the UserForm is unloaded. The normal Add will be to put the record in cells and expand RowSource to include it.
Add by AddItem
Dim iReply As Integer
Dim vComboEntry As Variant
If ComboBox1.ListIndex < 0 Then
iReply = MsgBox("Your entry is not part of the list." _
& "Do you wish to add it", vbYesNoCancel)
Select Case iReply
Case vbNo
'<carry on code>
Case vbYes
vComboEntry = ComboBox1.Value
ComboBox1.AddItem (vComboEntry)
'<carry on code>
Case vbCancel
ComboBox1.SetFocus
Exit Sub
End Select
End If
Again, note that with the AddItem method, even if Yes is clicked, the new entry will no longer be part of the list soon as the UserForm unloads!
The following RowSource method is slightly more complicated, but the new entry will be permanently part of the list and will be saved with the file.
Add and Expand RowSource
Dim iReply As Integer
Dim vComboEntry As Variant
If ComboBox2.ListIndex < 0 Then
iReply = MsgBox("Your entry is not part of the list." _
& "Do you wish to add it", vbYesNoCancel)
Select Case iReply
Case vbNo
'<carry on code>
Case vbYes
vComboEntry = ComboBox2.Value
Range("ComboBox_List").End(xlDown).Offset(1, 0) _
= vComboEntry
Range("ComboBox_List").Resize _
(Range("ComboBox_List").Rows.Count + 1).Name = "ComboBox_List"
ComboBox2.RowSource = "ComboBox_List"
'<carry on code>
Case vbCancel
ComboBox2.SetFocus
Exit Sub
End Select
End If
In this code, the Variant variable vComboEntry stores the value of the ComboBox. A Variant variable is used so the value can be either a number or text. The End(xlDown) method is used with Offset to place the entry on the first row below the current list. The Resize method is then used to redefine the ComboBox_List named range, then set RowSource once again to the named range.
No matter which method is used, it is important that the user have a final choice whether or not to add the entry to the list. The Yes/No/Cancel MsgBox is used in the above examples. If No is clicked, then the code continues without adding it. If Cancel, then focus is set back to the ComboBox to allow additional changes. If Yes, then the entry is added.
|
|
OzGrid.com accepts no responsibility for any adverse affect that may result from undertaking our training.