Userform: listbox and controls' data entry validation
 

Hi ,
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 entered data.
When running, say row 1 of listbox is selected. If you enter wrong data and
click another item in the listbox, row2, the message is displayed, but the
listbox updates to the new selection preventing the user to re-entrer the
data for his previous selection (row1). I have posted my code below.

I have tried the txt_Exit event too, but to no avail.
My real case scenario consists in many dataentry control on the side of the
listbox, so i cannot let the user loose all the data he has just entered.

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
'----------------------------------------------------------------

Userform: listbox and controls' data entry validation
 

use the text box (txt) change event to validate - the way you do it gets
over-ridden by the list selection change event being queued and then running
--- thats why you get your error message twice. I've simplified the code by
adding a second column to the list box that holds the value....

hope you enjoy this

note: same controls (txt,lbx,cmd) just simpler code

Option Explicit
Private Sub lbx_Click()
With lbx
txt = .List(.ListIndex, 1)
End With
txt.SetFocus
End Sub

Private Sub txt_Change()
If IsNumeric(txt) Then
With lbx
.List(.ListIndex, 1) = txt
End With
ElseIf Len(txt) 0 Then
txt = Left(txt, Len(txt) - 1)
Else
txt = ""
End If

End Sub
'Filling the listbox first
Private Sub UserForm_Initialize()
With lbx
.AddItem "aaa"
.List(0, 1) = 1
.AddItem "bbb"
.List(1, 1) = 12
End With
lbx.SetFocus
End Sub






"sebastienm" wrote:

Hi ,
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 entered data.
When running, say row 1 of listbox is selected. If you enter wrong data and
click another item in the listbox, row2, the message is displayed, but the
listbox updates to the new selection preventing the user to re-entrer the
data for his previous selection (row1). I have posted my code below.

I have tried the txt_Exit event too, but to no avail.
My real case scenario consists in many dataentry control on the side of the
listbox, so i cannot let the user loose all the data he has just entered.

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
'----------------------------------------------------------------

Userform: listbox and controls' data entry validation
 

Hi Patrick,
And thank you for looking into this issue.
I like the idea of storing the value in a hidden column of the listbox.

This 'Almost' works. The issue is that, in fact, the validation is more
complex that what i explained. First, there could be (and there are) several
entry controls which may depend on the selected item in the listbox, but more
important, the validation could be more complex.
Eg say a textbox only allows numbers from 100 to 150. Say the user tries to
type 123 which is valid. The txt_Change would occur when the first digit
('1') is type in and the message would prevent the user to type in the whole
number.

Any other idea?

Regards,
Sébastien
<http://www.ondemandanalysis.com


"Patrick Molloy" wrote:

use the text box (txt) change event to validate - the way you do it gets
over-ridden by the list selection change event being queued and then running
--- thats why you get your error message twice. I've simplified the code by
adding a second column to the list box that holds the value....

hope you enjoy this

note: same controls (txt,lbx,cmd) just simpler code

Option Explicit
Private Sub lbx_Click()
With lbx
txt = .List(.ListIndex, 1)
End With
txt.SetFocus
End Sub

Private Sub txt_Change()
If IsNumeric(txt) Then
With lbx
.List(.ListIndex, 1) = txt
End With
ElseIf Len(txt) 0 Then
txt = Left(txt, Len(txt) - 1)
Else
txt = ""
End If

End Sub
'Filling the listbox first
Private Sub UserForm_Initialize()
With lbx
.AddItem "aaa"
.List(0, 1) = 1
.AddItem "bbb"
.List(1, 1) = 12
End With
lbx.SetFocus
End Sub






"sebastienm" wrote:

Hi ,
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 entered data.
When running, say row 1 of listbox is selected. If you enter wrong data and
click another item in the listbox, row2, the message is displayed, but the
listbox updates to the new selection preventing the user to re-entrer the
data for his previous selection (row1). I have posted my code below.

I have tried the txt_Exit event too, but to no avail.
My real case scenario consists in many dataentry control on the side of the
listbox, so i cannot let the user loose all the data he has just entered.

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
'----------------------------------------------------------------