Userform - validate controls data when a listbox on the form

So it would be something like the following:

Option Explicit

Private StoredValue() As Double
Dim ErrorFound As Boolean   'Added this line ##########################################
Private CodeMode As Boolean 'to cancel events when they are
                            ' triggered from my code and not
                            ' from the user

'Filling the listbox first
Private Sub UserForm_Initialize()
   Dim i As Long, ttl As Long
   
   CodeMode = True  'prevent events
   
   With lbx
      .AddItem "aaa"
      .AddItem "bbb"
   End With
   
   ttl = lbx.ListCount
   ReDim StoredValue(0 To ttl - 1)
   For i = 0 To ttl - 1: StoredValue(i) = i: Next
   
   CodeMode = False
End Sub

'When listbox is clicked, show the corresponding
' value in the textbox txt
Private Sub lbx_Click()
   'Cancel event if CodeMode
   If CodeMode Then Exit Sub
   
   txt = StoredValue(lbx.ListIndex)
   
End Sub

'Now the user can edit the textbox
'Validation: check data when the usere leaves the textbox
'       it has to be a number else prevent the update
Private Sub txt_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
   Dim d As Double
   
   CodeMode = True 'cancel events
   
   'Validation
   On Error Resume Next
   d = CDbl(txt)
   If Err <> 0 Then
      MsgBox "you must enter a number"
      Cancel = True 'Cancel the update of the textbox
      ErrorFound = True                                 'Added this line #################
   Else
      StoredValue(lbx.ListIndex) = CDbl(txt)
      ErrorFound = False                                'Added this line #################
   End If
   
   CodeMode = False
End Sub

'Added this event to check if an error was found with the user's entry
Private Sub lbx_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If ErrorFound Then Cancel = True
End Sub

Hello  sebastienm,
   I just got home from work.. and got a chance to test what I had suggested and it does not work.. sorry about that...
   I will try to find a solution.....

tony_813

Hello... How about the following:

Option Explicit

Private StoredValue() As Double
Private CodeMode As Boolean 'to cancel events when they are
                            ' triggered from my code and not
                            ' from the user

Private Sub txt_AfterUpdate()
    StoredValue(lbx.ListIndex) = CDbl(txt)
End Sub

Private Sub txt_Change()
   Dim d As Double
   'Validation
   On Error Resume Next
   If txt = "" Then Exit Sub
   d = CDbl(txt)
   If Err <> 0 Then
      MsgBox "you must enter a number"
      lbx.Enabled = False
   Else
      lbx.Enabled = True
   End If
   CodeMode = False
End Sub

'Filling the listbox first
Private Sub UserForm_Initialize()
   Dim i As Long, ttl As Long
   
   CodeMode = True  'prevent events
   
   With lbx
      .AddItem "aaa"
      .AddItem "bbb"
   End With
   
   ttl = lbx.ListCount
   ReDim StoredValue(0 To ttl - 1)
   For i = 0 To ttl - 1: StoredValue(i) = i: Next
   
   CodeMode = False
End Sub

'When listbox is clicked, show the corresponding
' value in the textbox txt
Private Sub lbx_Click()
   'Cancel event if CodeMode
   If CodeMode Then Exit Sub
   txt = StoredValue(lbx.ListIndex)
End Sub

Hi tony_813,
Thank you for looking into this issue.

It had tried something similar to your first method above but to no avail.

Your last example *Almost* works. I say 'almost' because, in fact, my validations are more complex. Eg: a textbox may only allow values between 100 and 150. However the txt_Change event occurs for each digit entered in a box. That is, if the user tries to enter the number 123 which is valid, the _Change event would occur when the '1' is typed but since '1' is not valid, it would prevent the user to finish typing the whole number.

Any other idea?
And again, thanks for your time,

Sebastien

Hello Sebastien...

In addtion to the previous code...How about changing the following to the txt_AfterUpdate Event:

Private Sub txt_AfterUpdate()
    If txt.Value < 100 Or txt.Value > 150 Then
        MsgBox "Your Message"
        lbx.Enabled = False
    End If
    StoredValue(lbx.ListIndex) = CDbl(txt)
End Sub

tony_813

tony_813,
Sorry for not keeping track of this post in a timely maner.
As a short term fix, I actually implemented a "Modify" command button becoming enabled once a change has been made.
I am however still interested in finding another a regular implementation though.

Disabling the listbox was a good idea, but still, the conditions to re-enable it creates another problem:
Say the user has changed a textbox
The listbox is set to index 1
The user clicks index 2 in the listbox
- Txt AfterUpdate:
   The validation proceduire is called.
   - If True (valid) then let the controls process normally
   - If false: tell the user to correct the input with a "Continue or Abort entry"
       - If Abort, do not save entryu and controls process normally
       - If Continue, cancel the Lbx Click by Disabling the listbox.
          #### this is this last case that is cumbersome to implement ####
          The user could change an entry (Change event) which would re-enable the listbox.
          But if the user re-think at this point and in fact doesn't want to change anything, clicking on the list again will not re-display the "Abort or Continue" message because the listbox is disabled, so the user is stuck here (unless he changes something in the entry txt box).

At this point, my last shot will be to use win32 APIs with subclassing to capture the click event before it is processed by the listbox and determining or not whether the process of the event should continue based on the form validation.

If you have any other idea i'd be glad to hear it since using APIs is something i try to use as a last resort only.

Thank you for your patience,
Sebastien