Userform Control validity check

[Ken McLennan]

G'day there One & All,

I've Googled for a resolution to my issue, but without success. Others
asking about similar problems either have no response, or answers that
don't help me at all

I have a userform with a combobox; 2 textboxes; and 2 multiselect
listboxes. Altogether with some other bits & pieces it constitutes the
data entry form for a simple Excel database.

To ensure a valid record I require an entry in each of the controls.
When the "Enter Data" control button is clicked I have code which checks
each control for an entry. If any control has no entry, the background
changes to a bright colour & the record is not saved.

All seems to work fine until we come to the listboxes. They are being
cleared when read for the data validation. It makes no difference
whether any field has valid data or not, the listbox entries are cleared
every time (obviously, the other controls have no effect on them).

Is there anyway to make the entries a tad less volatile?

I'm using XL 2002 on XP. I don't recall what we use at work, but it
seems to be fully compatible with XP.

Thanks for reading this far,
Ken McLennan
Qld Australia

 

[GS]

Ken McLennan formulated on Friday :

G'day there One & All,

I've Googled for a resolution to my issue, but without success. Others
asking about similar problems either have no response, or answers that don't
help me at all

I have a userform with a combobox; 2 textboxes; and 2 multiselect listboxes.
Altogether with some other bits & pieces it constitutes the data entry form
for a simple Excel database.

To ensure a valid record I require an entry in each of the controls. When
the "Enter Data" control button is clicked I have code which checks each
control for an entry. If any control has no entry, the background changes to
a bright colour & the record is not saved.

All seems to work fine until we come to the listboxes. They are being
cleared when read for the data validation. It makes no difference whether any
field has valid data or not, the listbox entries are cleared every time
(obviously, the other controls have no effect on them).

Is there anyway to make the entries a tad less volatile?

I'm using XL 2002 on XP. I don't recall what we use at work, but it seems to
be fully compatible with XP.

Thanks for reading this far,
Ken McLennan
Qld Australia

A couple of things jump out at me...

1. Why is the "Enter Data" button enabled BEFORE the inputs are
validated?

2. What is the validation doing that it clears the listboxes?
<ergo: post your code>

 

[Ken McLennan]

G'day there Gary,

1. Why is the "Enter Data" button enabled BEFORE the inputs are
validated?

The user enters their data into the fields. They then click on "Enter
Data". "Validation" is actually poor terminology on my part. The code
checks for an entry to each field & nothing more; except for the
combobox which has its style set to fmStyleDropDownList so it only
accepts sets values.

If all fields have entries then the record is saved. If not, then the
offending field has its background colour changed and the record sits
there awaiting correction.

2. What is the validation doing that it clears the listboxes?
<ergo: post your code>

-----[ SNIP ]-----

Function CompleteRecord() As Boolean
Dim int_X As Integer
Dim str_X As String
' Boolean - False = 0; True = -1
Dim bool_A As Boolean, bool_B As Boolean, bool_C As Boolean, bool_D
As Boolean, bool_E As Boolean
CompleteRecord = True

With frm_Main
If Len(Trim(.ComboBox6.Value)) = 0 Then
bool_A = False
.ComboBox6.BackColor = 52479
Else
bool_A = True
.ComboBox6.BackColor = vbWhite
End If
If Len(Trim(.TextBox6.Value)) = 0 Then
bool_B = False
.TextBox6.BackColor = 52479
Else
bool_B = True
.TextBox6.BackColor = vbWhite
End If
If Len(Trim(.TextBox4.Value)) = 0 Then
bool_C = False
.TextBox4.BackColor = 52479
Else
bool_C = True
.TextBox4.BackColor = vbWhite
End If

str_X = ""
For int_X = 0 To .ListBox9.ListCount - 1
If .ListBox9.Selected(int_X) Then
str_X = str_X & .ListBox9.List(int_X)
End If
Next
If Len(str_X) = 0 Then
bool_D = False
frm_Main.ListBox9.BackColor = 52479
Else
bool_D = True
frm_Main.ListBox9.BackColor = vbWhite
End If

str_X = ""
For int_X = 0 To .ListBox10.ListCount - 1
If .ListBox10.Selected(int_X) Then
str_X = str_X & .ListBox10.List(int_X)
End If
Next
If Len(str_X) = 0 Then
bool_E = False
frm_Main.ListBox10.BackColor = 52479
Else
bool_E = True
frm_Main.ListBox10.BackColor = vbWhite
End If

CompleteRecord = bool_A And bool_B And bool_C And bool_D And bool_E

End With


End Function

-----[ UNSNIP ]-----

Here 'tis as requested. Any advice gladly accepted.

Thanks for looking at it,
Ken McLennan
Qld, Australia.

 

[GS]

Ken McLennan wrote :

G'day there Gary,


The user enters their data into the fields. They then click on "Enter Data".
"Validation" is actually poor terminology on my part. The code checks for an
entry to each field & nothing more; except for the combobox which has its
style set to fmStyleDropDownList so it only accepts sets values.

If all fields have entries then the record is saved. If not, then the
offending field has its background colour changed and the record sits there
awaiting correction.

Sorry I wasn't more clear. Normally, I would not allow the "Enter Data"
button to be enabled until all fields were validated as 'good-to-go'.
This precludes, then, that each control would validate user
inputs/selections in an event procedure (Change/Click or ?) and
increment a variable that totals the correct number of required
inputs/selections. Once this is reached the "Enter Data" button gets
enabled.

IOW, there's no way a user can execute processing the data until all
fields are validated. It just makes sense to me to validate user action
at the control level, NOT the button that processes the data.<g> I
guess it's a matter of logistics preference, but the methodology I use
follows what most seasoned VB developers do when working with data.
Also, most data controls follow this same convention.

2. What is the validation doing that it clears the listboxes?
<ergo: post your code>

-----[ SNIP ]-----

Function CompleteRecord() As Boolean
Dim int_X As Integer
Dim str_X As String
' Boolean - False = 0; True = -1
Dim bool_A As Boolean, bool_B As Boolean, bool_C As Boolean, bool_D As
Boolean, bool_E As Boolean
CompleteRecord = True

With frm_Main
If Len(Trim(.ComboBox6.Value)) = 0 Then
bool_A = False
.ComboBox6.BackColor = 52479
Else
bool_A = True
.ComboBox6.BackColor = vbWhite
End If
If Len(Trim(.TextBox6.Value)) = 0 Then
bool_B = False
.TextBox6.BackColor = 52479
Else
bool_B = True
.TextBox6.BackColor = vbWhite
End If
If Len(Trim(.TextBox4.Value)) = 0 Then
bool_C = False
.TextBox4.BackColor = 52479
Else
bool_C = True
.TextBox4.BackColor = vbWhite
End If

str_X = ""
For int_X = 0 To .ListBox9.ListCount - 1
If .ListBox9.Selected(int_X) Then
str_X = str_X & .ListBox9.List(int_X)
End If
Next
If Len(str_X) = 0 Then
bool_D = False
frm_Main.ListBox9.BackColor = 52479
Else
bool_D = True
frm_Main.ListBox9.BackColor = vbWhite
End If

str_X = ""
For int_X = 0 To .ListBox10.ListCount - 1
If .ListBox10.Selected(int_X) Then
str_X = str_X & .ListBox10.List(int_X)
End If
Next
If Len(str_X) = 0 Then
bool_E = False
frm_Main.ListBox10.BackColor = 52479
Else
bool_E = True
frm_Main.ListBox10.BackColor = vbWhite
End If

CompleteRecord = bool_A And bool_B And bool_C And bool_D And bool_E

End With


End Function

-----[ UNSNIP ]-----

Here 'tis as requested. Any advice gladly accepted.

I don't see anything here that would clear the listboxes.

To explain further what I was suggesting regarding the validations
being done by control events:

I would color the background of all controls that require
inputs/selections with a non-offensive color like light green or light
yellow. This provides a visible cue that these fields are mandatory.

I would validate the inputs in the textboxes/combobox in the
AfterUpdate event. Here I'd set the backcolor to white if inputs are
valid.

Private Sub ComboBox6_AfterUpdate()
With Me.ComboBox6
If Len(Trim(.Text)) > 0 Then _
lValidInputs = lValidInputs + 1: .BackColor = vbWhite
End With 'Me.ComboBox6
Me.cmdEnterData.Enabled = (lValidInputs = lREQD_FIELDS)
End Sub 'ComboBox6_AfterUpdate()

Private Sub TextBox6_AfterUpdate()
With Me.TextBox6
If Len(Trim(.Text)) > 0 Then _
lValidInputs = lValidInputs + 1: .BackColor = vbWhite
End With 'Me.TextBox6
Me.cmdEnterData.Enabled = (lValidInputs = lREQD_FIELDS)
End Sub 'TextBox6_AfterUpdate()

ListBoxes:
The way you test the multi-select listboxes is fine but why keep going
once you find a selected item? Using the listbox's Change event tells
you that a selection has happened, but not if an item was unselected,
and this will fire for each selection change. Instead, I'd use the
AfterUpdate event to run your loop, adding to the counter and exiting
the loop when the 1st selected item is found.

Private Sub ListBox9_AfterUpdate()
With Me.ListBox9
For int_X = 0 to .Listcount - 1
If .Selected(int_X) Then _
lValidInputs = lValidInputs + 1: .BackColor = vbWhite: Exit For
Next 'int_X
End With 'Me.ListBox9
Me.cmdEnterData.Enabled = (lValidInputs = lREQD_FIELDS)
End Sub 'ListBox9_AfterUpdate()

Now, your "Enter Data" button is only available AFTER all
inputs/selections are validated, and it only has to process the data.
The CompleteRecord function is no longer needed. The controls validate
themselves as the user interacts with each one.

HTH

 

[Ken McLennan]

G'day there Gary,

Sorry I wasn't more clear. Normally, I would not allow the "Enter Data"
button to be enabled until all fields were validated as 'good-to-go'.
This precludes, then, that each control would validate user
inputs/selections in an event procedure (Change/Click or ?) and
increment a variable that totals the correct number of required
inputs/selections. Once this is reached the "Enter Data" button gets
enabled.

That makes sense.

I don't see anything here that would clear the listboxes.

Neither did I but that didn't stop it from happening, unfortunately.
Even the severe blast of swear words I unleashed at it didn't help

I would color the background of all controls that require
inputs/selections with a non-offensive color like light green or light
yellow. This provides a visible cue that these fields are mandatory.

Fine idea, so I did that.

I would validate the inputs in the textboxes/combobox in the
AfterUpdate event. Here I'd set the backcolor to white if inputs are
valid.

Text & Combo boxes worked fine with this method. Thanks for that.

ListBoxes:
The way you test the multi-select listboxes is fine but why keep going
once you find a selected item? Using the listbox's Change event tells
you that a selection has happened, but not if an item was unselected,
and this will fire for each selection change. Instead, I'd use the
AfterUpdate event to run your loop, adding to the counter and exiting
the loop when the 1st selected item is found.

Private Sub ListBox9_AfterUpdate()
With Me.ListBox9
For int_X = 0 to .Listcount - 1
If .Selected(int_X) Then _
lValidInputs = lValidInputs + 1: .BackColor = vbWhite: Exit For
Next 'int_X
End With 'Me.ListBox9
Me.cmdEnterData.Enabled = (lValidInputs = lREQD_FIELDS)
End Sub 'ListBox9_AfterUpdate()

Unfortunately, the AfterUpdate event didn't fire. I did get something
happening with the Change event, but that led to another situation where
I could select/deselect items in the first listbox which incremented the
count allowing the Enter Data commandbutton to become active when there
were no entries in the second listbox. I should be able to adjust the
counting mechanism somewhere, but I've not had a chance to work on it
yet. I'm still confused as to why the AfterUpdate event didn't work.

Now, your "Enter Data" button is only available AFTER all
inputs/selections are validated, and it only has to process the data.
The CompleteRecord function is no longer needed. The controls validate
themselves as the user interacts with each one.

Thanks very much for your advice. It makes much more sense to do it the
way you suggested. I think it will be fine if I can figure out the
AfterUpdate bit. I'll have to work on it some more, but probably not today.

Thanks very much for your assistance. If you have any further ideas I
would certainly appreciate them, but if not (or if you actually have a
life) I'm sure you've pointed me in the right direction for which I'm
grateful - and have learned some useful techniques.

Thanks again
Ken McLennan
Qld, Australia

 

[GS]

Ken,
Thanks for the feedback!
I'll look into the AfterUpdate issue with the listboxes.
I did think you'd need a methodology to track what users do in terms of
deselecting already selected items.

I was thinking a 'Static' var could be used to store whether the count
was incremented already. This could take appropriate action if the list
item were deselected leaving the listbox in a 'requires input'
state. This is doable, I just need time to work on it.

I think you had the right idea about this using your If..Then..Else
constructs. I'm considering a modified version of that which will track
the input state of each control.