Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform Control validity check
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform Control validity check
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 -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform Control validity check
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform Control validity check
Ken McLennan wrote :
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. 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 -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform Control validity check
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform Control validity check
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[s] 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. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check the path for validity | Excel Programming | |||
How can I show a validity by a prompted symbol in colour? | Excel Worksheet Functions | |||
How do I add a control check box to control other checkboxes? | Excel Worksheet Functions | |||
=VLOOKUP validity | Excel Programming | |||
Selecting control on userform with part of control name (set question) | Excel Programming |