Why is this MyObject.setfocus not working? 1

[Chats]

Hi,

I have the following code which sits behind a userform in Excel VBA. What I want is that when the user clicks or tabs out of a textbox, the TestNumeric procedure is called, which checks to see if the values are numeric. If not, a messagebox is produced and the cursor focus is sent back to the original textbox.

My code works fine, except that the cursor focus is not going back to the original textbox, but staying with whatever object has been clicked.

Can anybody help?

Here is my code:-

Dim MyObject As Object

Private Sub NumberOfFloors_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Set MyObject = NumberOfFloors
    Call TestNumeric(MyObject)
End Sub


Private Sub TestNumeric(Object)
    If Object.Value <> "" And Not IsNumeric(Object.Value)Then
       MsgBox ("Enter numeric values only")
            Object.SetFocus
    End If
End Sub

 

[ZmrAbdulla]

Private Sub TestNumeric(Object)
If Object.Value <> "" And Not IsNumeric(Object.Value)Then
MsgBox ("Enter numeric values only")
[COLOR=green]'Set focus to another control then the actual[/color]
[b]AnotherControl.SetFocus '[/b]
Object.SetFocus
End If
End Sub

Zameer Abdulla
_{Jack of Visual Basic Programming, Master in Dining & Sleeping}
Visit Me

 

[Chats]

Zameer,

Thanks, but I just tried this - my new code looks like this

Dim MyObject As Object

Private Sub NumberOfFloors_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Set MyObject = NumberOfFloors
    Call TestNumeric(MyObject)
End Sub


Private Sub TestNumeric(Object)
    If Object.Value <> "" And Not IsNumeric(Object.Value)Then
       MsgBox ("Enter numeric values only")
       
       Textbox1.SetFocus
       Object.SetFocus
    End If
End Sub

But what happens now is that the msgbox is produced twice, and the focus remains with Textbox1 afterwards.

Was this what you meant for me to do?

 

[PHV]

Try to play with the NumberOfFloors_BeforeUpdate event procedure instead of the Exit event.
No SetFocus problem, play with the Cancel argument instead.
Hint: make TestNumeric a function returning a boolean value.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Chats]

PH,

Excellent, thanks!

With a little bit of experimenting I now have the following code which works perfectly (and is a much neater way of doing things)

Dim MyObject As Object

Private Sub NumberOfFloors_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If TestNumeric(NumberOfFloors) = 1 Then
        MsgBox ("This is not a number")
        Cancel = True
    End If
End Sub

Private Function TestNumeric(Object)
    
    If Object.Value <> "" And Not IsNumeric(Object.Value) Then
        TestNumeric = 1
        Else: TestNumeric = 0
    End If
    
End Function