 |
| <<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>> |
|---|
|
|
|
Workbook
Download
This is a zipped Excel Workbook to go with this lesson.
Validating The Users Inputs
In the last lesson we looked at how a MultiPage control could be used. There are no hard and fast rules for which controls should be used for what purpose, only guides. If the truth be known the only limit to this is sometimes your own imagination and ability as a developer. As a developer myself I have seen many Excel projects that have incorporated UserForms. Some of these look great, but certainly don't live up to their expectations. In other words they are all gloss and no guts. As you may now be starting to realize, formatting a UserForm to look great is not that hard! This is a bit like those fantastic looking charts that really tell us nothing. From my personal experience, if you develop a UserForm for others to use, keep it gray and only add the 'bells and whistles' (if you must) once it has been proven to work. To show you some examples of what I mean by this, take a good look around Excel itself. Each time you select a menu option that shows a UserForm (Tool>Options) you don't see a multitude of colors thrust at you simply because Excel is used by millions of people all around the world. To try and second guess the favourite color of these users would be silly. So while you may make your UserForm look great in your eyes, don't assume another user will think the same. What I am saying in whole is spend that extra effort on the functionality of the UserForm and not the presentation. Gray is good!
One of the biggest problems that is (or should be) faced by a developer (other than understanding just what they want) is developing a project that will only accept valid data. By far the quickest and easiest way to achieve this is to use a ListBox and/or a ComboBox that presents the user with only valid data. The approach is one that I will try to employ whenever possible. There is also an added bonus to doing this and that is the user does not need to type, which means no typos! To ensure the user has made a valid selection from a ComboBox (and not typed in something not from the list) use the ListIndex Property of the ComboBox.
If ComboBox1.ListIndex < 0 Then
MsgBox "Your entry must be part of the list", vbCritical
Exit Sub
End If
Why 0 ? because the ListIndex value of the first row in a list is 0, the value of the second row is 1, and so on....
This very simple method is virtually foolproof and is a very easy way to ensure the entry in the ComboBox is part of the list. There are 2 important points to note here:
A message box tells the user what the problem is. Don't use a message like "Invalid Data!" only as the user will be left wondering why.
We have used "Exit Sub" this will cause our code to go no further and stop the rest of the Procedure from running. Don't use the keyword End for this as this will not only stop the Procedure from running, but unload the entire UserForm. Quite annoying if the user has just inputted into 20 or so TextBoxes, ComboBoxes etc as they will loose the lot!
Sometimes you may want to add this new entry to the ComboBox list if it's not already part of the list. This will depend on how the list is being read into the ComboBox.
AddItem
RowSource
If it's via the AddItem method then this is very easy just use:
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
If it's via the RowSource
method then use:
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
The most important part of both of these codes is that we allowed the user to make a choice as to whether they want to add their entry to the list or not. If they say No, then we should carry on without adding it. If they say Cancel we place them back in the ComboBox (ComboBox1.SetFocus) and let them make any changes. If they say Yes then we add it. The AddItem method is pretty straightforward, but you should realize that even if they say Yes, the new entry will no longer be part of the list soon as the UserForm unloads! the RowSource method is slightly more complicated, but the one I would opt for in most cases as the new entry will be permanently part of the list.
What we have done in the case of the RowSource code is used the Variant variable "vComboEntry
" to store the value of the ComboBox. We used a Variant so that it can be a number or text. We then used the End(xlDown) method with the Offset to place our new entry at the bottom outside of the named range "ComboBox_List". As soon as the new entry was added to the outside of the named range we used the Resize Method to re-define our named range. The Resize method takes 2 arguments, both of which are optional:Row Size
Column Size
We used "Range("ComboBox_List").Rows.Count + 1" to resize the rows in ComboBox_List by one row, this accounts for the new entry. We then immediately reset the RowSource of the ComboBox back to "ComboBox_List". This is needed for our ComboBox to reflect the new range.
You could if you prefer use a Dynamic range for your ComboBox, but you will still need to reset the RowSource. Dynamic Ranges