OnKey not working when a textbox is selected....

[markronz]

Hello everyone-
I have inserted two Control Toolbox text boxes directly onto an excel spreadsheet. All I want to be able to do is use the TAB key to switch between the two textboxes. Since there is no way to set up any sort of tab order for the two text boxes when they are placed directly onto a spreadsheet, I'm trying to come up with some kind of work around. Below is my code, which isn't working. What I was trying to do was, once the text box gets focus, and the tab key is pressed, to call a different procedure.

Private Sub TextBox1_GotFocus()
    Application.OnKey "{TAB}", "TabNext"
End Sub


Sub TabNext()
    MsgBox "Why doesn't this work?!"
End Sub

No doubt there are a number of problems with my code. Does anyone have any idea if there is a way to fix this? Or some other way to make a work around?

Thanks!
-Mark

 

[markronz]

Nevermind. I was able to get my work around by using the following code:

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = 9 Then
        TextBox2.Activate
    End If
End Sub

Thanks anyway!

 

[SkipVought]

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = 9 Then
        TextBox2.Activate
    End If
End Sub
Private Sub TextBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = 9 Then
        TextBox1.Activate
    End If
End Sub

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}