UserForm in Excel via VBAcode

[Theo]

I have got a Userform called UserForm1 created for data entry. There are several checkes in this form and the only (so far) thing that doesn't work is the selection of the focus.
For example:
If the code entered in TextBox1 is not correct a errorbox (msgbox) appears. When the users clicks on the OK button the user must return to TextBox1 in stead of continue on TextBox2.

Thanks for the help.

Theo
A desperate one...

 

[Theo]

I'm so very sorry, I feel like a nurd but it still doesn't do the job.
The validation works perfectly, but I still don't see the blinking cursor?!?!?!

A very very sad and shameful Theo

 

[Mudface]

A very very sad and shameful Theo

Nope, that should be me, I'm sorry but I could have sworn that worked alright with Excel 2002 at work (I'll check tomorrow), but with Excel 2000 at home it doesn't....

It appears the MsgBox is recieving the focus and, no matter what I've tried, it won't go back to the textbox. Two workarounds- either remove the MsgBox alerts in the code above, or add a label to your userform (Label1 below) and use this code: -
<pre>
Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

If Not Validated(TextBox1.Text) Then
Cancel = True
Label1.Caption = "Invalid entry"
Else:
Label1.Caption = ""
End If

End Sub

Private Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

If Not Validated(TextBox2.Text) Then
Cancel = True
Label1.Caption = "Invalid entry"
Else:
Label1.Caption = ""
End If

End Sub
Public Function Validated(myString As String) As Boolean

If myString <> "Theo" Then Validated = False: Exit Function
' Change to ucase(mystring) = "THEO".... if not bothered about how it's entered
Validated = True

End Function
</pre>

 

[Mark O'Brien]

Mudface's code worked fine on my Excel 2000.

On 2002-10-14 11:10, Mudface wrote:
Hope this works OK for you, place in the userform module and remove your existing validation code (save a copy of your workbook in case): -
<pre>
Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

If Not Validated(TextBox1.Text) Then
Cancel = True
MsgBox "Incorrect entry"
End If

End Sub

Private Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

If Not Validated(TextBox2.Text) Then
Cancel = True
MsgBox "Incorrect entry"
End If

End Sub
Public Function Validated(myString As String) As Boolean

If myString <> "Theo" Then Validated = False: Exit Function
' Change to ucase(mystring) = "THEO".... if not bothered about how it's entered
Validated = True

End Function
</pre>

 

[Mudface]

Yup, works fine on Excel 2002 at work, but it didn't on Excel 2000 at home. No idea why it should do so yet also work OK on Mark's Excel 2000.

Sorry, Theo, I'll have a quick look through MS support and see if there's anything there.

 

[Theo]

Mudface,

I now use your code without the msgbox function. And it works!!!
I think it should work with a alertbox but without is fine for now.

Thanks for the help so far.

A little question:
Do you know a simple and easy way to display a text file from a vba procedure running under MS Excel??

Kind regards,
Theo

 

[danspen]

Would this code be adaptable to, be between a certain range of numbers a, worksheet, and to validate to make sure a number is above 0 .