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

 

[Mudface]

Try writing your validation code in the Before_Update event of the Textbox. For example: -
<pre>
Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

If Not IsNumeric(TextBox1) Then
Cancel = True
MsgBox "Please enter a number"
TextBox1 = ""
End If

End Sub
</pre>

 

[Theo]

Mudface,
This is not exactly what I mean. When I put this code in it, it doesn't put my blinking cursor back to the textbox.
I've tried to use the SetFocus but this won't work either.

???

Thanks anyway,
Theo

 

[Andrew Poulsom]

Try:

TextBox1.SelLength = 0

 

[Mudface]

The code above just validates the textbox once a change has been made to it and the user attempts to tab or move into the next textbox. If the entry is invalid it flashes up a message, clears the textbox and puts the cursor back into it. I always find it easier to do this as I go along, rather than let the user put in what they like and then try and check everything at the end when they press a command button.

Having said that, the following will select the text in textbox1, if it's not numeric, adapt as necessary: -
<pre>
With TextBox1
If Not IsNumeric(.Text) Then
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
End If
End With
</pre>

 

[philR]

If you are running your validation from a private sub textbox1_enter(blah blah) or something similar, you will probably have a line something like:
if textbox1<>"Whatever" then
msgbox("Oops, try again")
textbox1.SetFocus
EndIf
All you need to do is include a Cancel=true to stop the _enter event from completing its task and switching the focus to the next object on the form. I don't think it matters where you put it, but I would put it just before the endif.

Hope this helps.

 

[Theo]

Thanks for all the reply's on my problem. You've been a great help so far.
But the problem of the lost cursor is still there.
When I try all of your suggestions, the procedure doesn't go further and it looks like the TextBox1 is still activated but I don't see the cursor blinking. When I try to type something, nothing appears on the screen of course...

A very sad Theo

 

[Mudface]

Hmm, I don't understand why not, could you post up the code you have and what exactly you have on your userform in the way of controls.

To demonstrate the above pieces of code, create a new userform, and add 3 textboxes and a command button to it. Double-click on the command button and copy and paste in the following: -
<pre>
Private Sub CommandButton1_Click()
With TextBox1
MsgBox "Hi"
' .SelStart = 0
' .SelLength = Len(.Text)
.SetFocus
End With
End Sub
</pre>

Remove the Rem marks to select the text and set the focus to TextBox1 whenever you click on the command button.

 

[Theo]

The way you suggest now works fine. Perfectly I must say, but...
Thats not exactly what I want. When the user fills the textbox1 field and enters the TAB or ENTER key a validation should occur. The validation works fine but the problem stays.

I've put the code from my form herein.

VERSION 5.00
Begin {C62A69F0-16DC-11CE-9E98-00AA00574A4F} UserForm4
Caption = "UserForm4"
ClientHeight = 3225
ClientLeft = 45
ClientTop = 330
ClientWidth = 4710
OleObjectBlob = "UserForm4.frx":0000
StartUpPosition = 1 'CenterOwner
End
Attribute VB_Name = "UserForm4"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False

Sub valideren()
With TextBox1
If TextBox1.Value = "theo" Then
MsgBox "Hi"
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
Cancel = True
End If
End With

End Sub


Private Sub TextBox1_AfterUpdate()
Call valideren
End Sub

I hope you understand my problem. The file above is only a example... the original is too long.

Theo

 

[Mudface]

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>