UserForm in Excel via VBAcode

Theo

New Member
Joined
Mar 14, 2002
Messages
15
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...
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
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

New Member
Joined
Mar 14, 2002
Messages
15
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
 

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339

ADVERTISEMENT

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

Active Member
Joined
Feb 25, 2002
Messages
257
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

New Member
Joined
Mar 14, 2002
Messages
15

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
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

New Member
Joined
Mar 14, 2002
Messages
15
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

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
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>
 

Forum statistics

Threads
1,144,446
Messages
5,724,400
Members
422,550
Latest member
Parvin_B

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top