Stay in TextBox - UserForm

I have a UserForm and for a TextBox I have the following code associated with it:

[VBA]

If IsNumeric(TextBox130.Value) Then

TextBox130.Value = Round(TextBox130.Value, 0)
JobArea(0).SqFt = TextBox130.Value
Call UpdateSqFtTotal

Else

MsgBox ("Please input a Number!"), vbInformation, "Data Input"
TextBox130.Text = ""

End If

[/VBA]

I want the cursor to return to the TextBox so that the user can start entering the correct data.

I have this code in a AfterUpdate(). I also tried putting it in an Exit() with Cancel set to True but I am not getting what I want.

Suggestions or questions.
Thanks

Try this:
[vba]
Me.TextBox130.SetFocus
[/vba]

That does not work. I am still not staying in the textbox I was before going to the msgbox. Is there a way to use tabindex to move around so that I could tell the cursor to be in the textbox indicated by the tabindex?

Thanks

Can you post an attachment?

DRJ

The code I sent you is what I am using. What kind of an attachement do you want?

Hi Nils,
I see the problem. Basically you're trying to return to a textbox within a macro triggered by Exit or AfterUpdate. I don't have a workaround at the moment, but I'll give it some thought.
Regards
Malcolm

Malcolm,

Here is a new copy of my code. The problem I have discovered appears to be that the cursor is not present. I believe that the focus is still at the TextBox130 after the MsgBox appears and you hit OK but there is no cursor indication. If you type, nothing appears in any box. If you click anyplace else, you get the MsgBox for the TextBox130.

I placed a breakpoint at the If statement and the MsgBox and after the MsgBox nothing happens when you type. The cursor is not blinking any place on the UserForm. So that is why I feel I have lost focus.

I am using Excel 2000. I did have the TexBox130 = "" after the MsgBox and I also had TextBox130.SetFocus but it did not help.

If you remove the MsgBox code, the code works fine. If you put in a character, it is erased and the cursor is at the TextBox ready for input. The MsgBox appears to be causing the problem.

Any suggestions?

Code:

---

Private Sub TextBox130_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim MyPrompt As String
MyPrompt = "Please input a Number!"
 
If IsNumeric(TextBox130.Value) Then
 
TextBox130.Value = Round(TextBox130.Value, 0)
JobArea(0).SqFt = TextBox130.Value
Call UpdateSqFtTotal
 
Else
Cancel = True
TextBox130.Text = ""
 
MsgBox MyPrompt, vbInformation, "Pro Paver Installer Data Input"
End If
End Sub

---

Hi Nils,

I put this userform together with your code (slightly modified for output) and it works for me with or without the message. I've tried it in 2003 and 2000. Do you still get the same problem?

The trouble with trying to set focus like this is that it happens but then gets overridden by later actions before control is returned to the user. Input validation should go in the BeforeUpdate event and Cancel = True set if it fails - this suppresses all the queued actions and returns immediately to the user with the focus still in the textbox. As a general rule, non-validation code using the input should go after update so you really want to split your code into two, something like ..

Code:

---

Sub Text130_BeforeUpdate(Cancel as Boolean)
    If IsNumeric(etc ... ... ... Then
        Cancel = True
    End If
End Sub

Sub Text130_AfterUpdate()
    :
:
    Call YourRoutine
End Sub

---

Sorry I pressed the wrong button and when I edit the post I can't see the rest of the thread so can't cut and paste all the relevant bits of code - I hope you can follow it.

I like you suggestion and I will give it a try later.

I did post my problem on another form and they tested a sample of the code that did not work on my machine with Excel 2000 but it worked on their 2003 and 2000. Could it be that my Excel is configured differently or some option set differently?

I am frustrated with the problem. But I did read about BeforeUpdate and I would think that it should also work. It will be interesting to see if it does. I will post my code after I have time to test it.

Thanks for the suggestion.

Tony, is this what you suggested?

[VBA]
Private Sub TextBox130_AfterUpdate()

If IsNumeric(TextBox130.Value) Then

TextBox130.Value = Round(TextBox130.Value, 0)
JobArea(0).SqFt = TextBox130.Value
Call UpdateSqFtTotal

End If
End Sub

Private Sub TextBox130_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

Dim MyPrompt As String
MyPrompt = "Please input a Number!"

If Not IsNumeric(TextBox130.Value) Then

Cancel = True
TextBox130.Text = ""
MsgBox MyPrompt, vbInformation, "Pro Paver Installer Data Input"

End If

End Sub
[/VBA]

I get the same results with this...It doesn't work. I believe that this should have worked after reading about the way BeforeUpdate works.

I also believe now that there is something wrong with my Excel.

Open to suggestions!

Try this

Private Sub TextBox130_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

With TextBox130
If KeyCode = 9 Or KeyCode = 13 Then

'It will stay in TextBox130 no matter you press Enter or TAB
KeyCode = 0

'These 2 statements will highlight the text. It's helpful for correcting invalid value
.SelStart = 0
.SelLength = Len(.Text)
End if
End With

End Sub

I had a similar problem which was fixed with the following code:

Private Sub TbNewDolphinName_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
'Check if name in use?
Dim X
Set ExistingName = Worksheets("Dolphins_Locations").Range("ExistingNames").Find(TbNewDolphinNa me.Value)
If Not ExistingName Is Nothing Then


X = MsgBox("The name '" + TbNewDolphinName.Value + "'" + " already exists choose another one.", vbOKOnly, "Enter another name")

TbNewDolphinName = TbNewDolphinName + " ?"
Cancel = True

End If
End Sub