Hi guys,
I have an issue with userform and validation of data (not on an excel sheet here)
On the userform, Userform1, I have:
- a listbox called lbx
- a textbox called txt
- a command button called cmd
I fill the listbox during _Initialize and check the data in txt_BeforeUpdate(Cancel) and cancels the update if not valid data.
Now, if the data is not valid, i warn the user with a message and i'd like to give him a chance to change the enter data.
When running, if you enter data and click another item in the listbox, the message is displayed, but the listbox updates to the new selection preventing the user to re-entrer the data for his previous selection. I have posted my code below.
I have tried the txt_Exit event too, but to no avail.
Any idea would be greatly appreciated.
Thanks,
Sebastien
Here is the code:
'-------------------------
----------
----------
----------
----------
----------
----
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
'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
Else
StoredValue(lbx.ListIndex)
= CDbl(txt)
End If
CodeMode = False
End Sub
'-------------------------
----------
----------
----------
---------