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" <(E-Mail Removed)> wrote in message
news:EF2AA225-B0CF-484F-B77A-(E-Mail Removed)...
> 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" wrote:
>
>> You're setting Cancel in a sub which has no reference to it: it's not a
>> global variable.
>>
>> Tim
>>
>> "Sam Kuo" <(E-Mail Removed)> wrote in message
>> news:1593F33F-CF7A-4F43-B378-(E-Mail Removed)...
>> >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
>>
>>
>>
|
Similar Threads
