CODEWITHSUNDEEP
excelvbatextboxuserform
newacc2240
newacc2240

Reputation: 1425

Arrow key in a textbox of a userform, VBA

I'm working on a userform with several textboxes, which are arranged just like an excel chart. I've set the TabIndexes, and they work perfectly with TAB/shift+TAB. However, the react of pressing arrow keys is not what I expected. I named those textboxes like this:

boxA1 boxB1 boxC1 boxD1
boxA2 boxB2 boxC2 boxD2 ...
boxA3 boxB3 boxC3 boxD3
          :
          :

Assuming that the focus is at boxB1. When pressing the down arrow key, I wish the focus goes to boxB2, but it will go to boxA3 or something.

I've tried this code:

Private Sub boxB1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Select Case KeyCode
        Case vbKeyDown
            boxB2.SetFocus
        Case vbKeyUp
            boxA10.SetFocus
    End Select
End Sub

It works fine, however, if there are only several textboxes in my form, this is a great and clear solution. But there are about 70 textboxes in my form, this will make my code large and really duplicated. Are there any parameters I can adjust to make it right? Or is there any function I can use such as below? Just sudo, you know.

Private Sub name_i_KeyDown(ByVal KeyCode as MSForms.ReturnInteger, ByVal Shift As Integer)
    Select Case KeyCode
        Case vbKeyDown
            Controls("name_" & i+1).SetFocus
        Case vbKeyUp
            Controls("name_" & i-1).SetFocus
    End Select
End Sub

Thank you!

Upvotes: 1

Views: 3027

Answers (1)

Zac
Zac

Reputation: 1944

You are on the right path. Add the below two UDF's in your form module:

Sub SetTextBoxFocus(ByVal KeyCode As MSForms.ReturnInteger)
    Dim sPrevControl As String
    Dim sNextControl As String
    Dim sBoxToSet As String
    Dim oC As Control

    ' Cater for TAB key press. Setting it so that it behaves like vbKeyDown. Remove this If condition if not required
    If Asc(KeyCode) = 57 Then
        KeyCode = vbKeyDown
    End If

    ' We only want to check if pressed key was either vbKeyDown or vbKeyUp and the key was pressed on a TextBox
    If TypeName(Me.ActiveControl) = "TextBox" And (KeyCode = vbKeyDown Or KeyCode = vbKeyUp) Then

        With Me.ActiveControl

            ' Lets set both previous and next text box names
            If InStr(1, .Name, "boxD") > 0 Then
                sPrevControl = "boxC" & GetNumFromString(.Name)
                sNextControl = "boxA" & GetNumFromString(.Name) + 1
            ElseIf InStr(1, .Name, "boxA") > 0 Then
                sPrevControl = "boxD" & GetNumFromString(.Name) - 1
                sNextControl = "boxB" & GetNumFromString(.Name)
            Else
                sPrevControl = "box" & Chr(Asc(Mid(.Name, 4, 1)) - 1) & GetNumFromString(.Name)
                sNextControl = "box" & Chr(Asc(Mid(.Name, 4, 1)) + 1) & GetNumFromString(.Name)
            End If

            ' Bases on which key was pressed, lets set the name of the text box to move to
            Select Case KeyCode
                Case vbKeyDown: sBoxToSet = sNextControl
                Case Else: sBoxToSet = sPrevControl
            End Select

            ' Loop through all controls in the form and set the focus to the control if found
            For Each oC In Me.Controls
                If oC.Name = sBoxToSet Then
                    oC.SetFocus
                    Exit For
                End If
            Next

        End With
    End If

End Sub

Function GetNumFromString(ByVal txt As String) As String
    Dim oRe As New RegExp

    With oRe
        .pattern = "\d"
        If .Test(txt) Then GetNumFromString = .Execute(txt)(0)
    End With

End Function

Now, in KeyDown for each TextBox, make the following call: SetTextBoxFocus KeyCode. This should do the trick

NOTE: I have boxD as the last text box. You should change that to whatever the last text box you have in the same row

Upvotes: 1

Related Questions