Cancel = True

[Sam Kuo]

I tried to use Cancel = Ture (as shown below) to ensure the user inputs a
value in 10 textboxes (namely txtCatchment1, txtCatchment2, ...,
txtCatchment10).
But my attempt returns an error at the line "Cancel=True". How should I fix
this?

' Below is in UserForm1
' (duplicate 10 times with the number changed to match the textbox name)
Private Sub txtCatchment1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
CheckInput(1)
End Sub

' Below is in Module1
Sub CheckInput(CatchmentNo As Long)
Dim strCatchmentNo As String
With UserForm1
strCatchmentNo = .Controls("txtCatchment" & CatchmentNo).Value
If Len(strCatchmentNo) > 0 Then
Else
Cancel = True
MsgBox "You must enter a value",
vbOKOnly + vbExclamation, "Entry Required"
End If
End With
End Sub

 

[Tim Williams]

You're setting Cancel in a sub which has no reference to it: it's not a
global variable.

Tim

 

[Sam Kuo]

Hi Tim
Thanks for your explantion.
I've now have the Cancel in the private sub and it works fine. But I just
wonder if there is a shorter way to achieve what I want to do, without having
to manually duplicate the code 10 times? Thanks in advance.

' Below is in UserForm1
' (duplicate 10 times with the number changed to match the textbox name)
Private Sub txtCatchment1_BeforeUpdate(ByVal Cancel As
MSForms.ReturnBoolean)
Dim strCatchmentNo As String
Const CatchmentNo As Long = 1
With UserForm1
strCatchmentNo = .Controls("txtCatchment" & CatchmentNo).Value
If Len(strCatchmentNo) > 0 Then
Else
Cancel = True
MsgBox "You must enter a value",
vbOKOnly + vbExclamation, "Entry Required"
End If
End With
End Sub

 

[Tim Williams]

Something like this ?

'********************************************
Private Sub txt1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Checkit 1, Cancel
End Sub

Private Sub Checkit(num As Integer, ByRef Cancel As MSForms.ReturnBoolean)

Dim strCatchmentNo As String
With UserForm1
strCatchmentNo = .Controls("txt" & num).Value
If Len(strCatchmentNo) > 0 Then
Else
Cancel = True
MsgBox "You must enter a value"
End If
End With

End Sub
'*********************************************
However, from a usability perpective this can be frustrating for users: it
may be better to have a single validation process which runs (eg) when your
user clicks "OK". Eg. what should they enter if they don't know what to
enter and have to go off and look it up ? With this model they're stuck in
that particular textbox unless they enter a value.

Tim

 

[Sam Kuo]

Thanks Tim.
Your comment on the usability issue is also worth thinking. Thanks
Sam