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

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

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