Detect find what is the current text box

[michaelg]

Hi,

I have a number of text boxes embedded on a worksheet (not in a user form) and I want to find out what is the current text box after a key is pressed.

My goal:

To reduce the amount of code to perform a tab next activity. At the moment, I know about the code below which works well

Private Sub txtbox_criteria_and_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
'
' This function moves cursor away from txt_criteria_and textbox, when
' TAB or Enter is pressed to "next field", or when
' SHIFT + TAB or SHIFT + ENTER is pressed to "previous field".
'
    If KeyCode = vbKeyTab Or KeyCode = vbKeyReturn Then
        If CBool(Shift And 1) Then
            ' previous field
            txtbox_criteria.Activate
        Else
            ' next field
            txtbox_criteria_or.Activate
        End If
    End If
End Sub

...however this requires the same cut/paste of the code for each text box.

My thought is to have 2 functions;

#1 - detects when the TAB or TAB + SHIFT is detected, I have found that the code below can do this well.

#2 - inside this function it calls another function, say tabshift() and passes the name of the "current" text box as an arguement to the TABSHIFT() function. Actually it would be, TABSHIFT(NEXT,[fieldname]), or TABSHIFT(BACK,[fieldname])

The TABSHIFT() function would then have a list of cases, if arguement field is "X" and direction is "NEXT" then shift to "Y" etc.

Option Explicit

Private Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If GetKeyState(vbKeyShift) < 0 And GetKeyState(vbKeyTab) < 0 Then
        MsgBox "Shift + Tab pressed"
    Else
        If GetKeyState(vbKeyTab) < 0 Then
            MsgBox "Tab Pressed"
        End If
    End If
End Sub

At the moment, I'm stuck on how to extract what the current text box field is, ie get the name of what the current focus is.

Regards
Michael G

 

[Andrew Poulsom]

The Worksheet_SelectionChange won't fire when the user presses Tab in a TextBox.

It may be possible to use a Class module to achieve what you want (ie a single Keydown event procedure for all the TextBoxes), but I would stick with code for each TextBox as in:

http://www.mrexcel.com/board2/viewtopic.php?t=87996