CODEWITHSUNDEEP
vbaexcelexitmultipage
sabarigiri
sabarigiri

Reputation: 57

text box exit event in multipage userform

I have multipage user form which has sub multipage. the text box exit event is not working it is working when userform exits. So I have tried the after update event. It works fine but I am not able to set focus the textbox if the entered value is not numeric. the code is working fine. userform doesnt show the previos page text box. below is the screen shot and code multipage screenshot

At page no 4 I have text box and once the details entered,I click the page2 to proceed. before that I need to check whether the entered detaisl is numeric or not. If its not numeric I have to show the page4 and focus on text box1 to re-enter the details that is not working. please help me guys.

Private Sub TextBox3_AfterUpdate()
  If Len(UserForm1.TextBox3.Value) <> 0 And _ 
    IsNumeric(UserForm1.TextBox3.Value) = False Then
    UserForm1.MultiPage1.Value = 0
    userform1.multipage2.value = 1
    UserForm1.TextBox3.SetFocus
    MsgBox "Only Numbers are Allowed!!"

  End If
End Sub

the code didnot show error but Its not focusing on the text box still showing the page2.

Upvotes: 0

Views: 996

Answers (2)

T.M.
T.M.

Reputation: 9948

Messagebox interrupts set Focus

Displaying a messagebox (window) interrupts your SetFocus code. In order to work around this issue just redisplay the userform after hiding via

Me.Hide: Me.Show

Another approach would be to display the error message by a Label caption message.

Modified example using your original code:

Private Sub TextBox3_AfterUpdate()
If Len(Me.TextBox3.Value) <> 0 And _
   IsNumeric(Me.TextBox3.Value) = False Then
     Me.MultiPage1.Value = 0
     MsgBox "Only Numbers are allowed!!"
     Me.MultiPage2.Value = 1
     Me.TextBox3.SetFocus

     Me.Hide: Me.Show     ' << work around by redisplaying userform
End If
End Sub

Hint

It's better to use the Me. prefix than UserForm1. within the userform code module itself to identify controls (and allow IntelliSense).

Upvotes: 1

Taylor Raine
Taylor Raine

Reputation: 578

You could consider using keydown events to prevent non numeric input all together with something like this

''  allows checking numlock 
Private Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
Private Const ksCapital As Long = 20
Private Const ksNumLock As Long = 144


Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Select Case KeyCode
        Case vbKeyBack, vbKeyDelete, vbKeyLeft, vbKeyRight, vbKeyInsert, vbKeyNumlock
            ''  allow deleting, moving, changing numlock
        Case Asc(0) To Asc(9)
            ''  if input is a number, not a symbol let it remain
            If Shift Eqv Not GetKeyState(ksNumLock) Then KeyCode = 0
        Case Asc("-")
            ''  allow negatives
            If InStr(1, Me.TextBox1.Text, "-") > 0 Or Me.TextBox1.SelStart > 0 Then Let KeyCode = 0
        Case Asc(".")
            ''  allow decimals
            If InStr(1, Me.TextBox1.Text, ".") > 0 Then Let KeyCode = 0
        Case Else
            ''  allow nothing else
            Let KeyCode = 0
    End Select
End Sub

Upvotes: 1

Related Questions