CODEWITHSUNDEEP
excelvbasetfocus
Pasquale Prudente
Pasquale Prudente

Reputation: 47

TextBox and SetFocus VBA Excel

I have 3 couples of textboxes. I need to move the cursor on the first textbox of which the second textbox is not empty.

For example, I have the sixth textbox not empty and I need to move the cursor on the fifth textbox. I tried using SETFOCUS method but the cursor doesn’t move as I would like. I use AFTER UPDATE event. How it is possible to solve this problem?

Following similar code

Private Sub txtMain_AfterUpdate()
 
  Select Case True

        Case  txt2.Text <> ""
                txt1.SetFocus
        Case txt4.Text <> ""
                txt2.SetFocus
        Case txt6.Text <> ""
                txt5.SetFocus
        End Select
End sub

I tried using Change and Exit event

Upvotes: 0

Views: 330

Answers (2)

Pasquale Prudente
Pasquale Prudente

Reputation: 47

Hi all and thank you everyone. I have solved using KEYDOWN EVENT.

Thank you

Upvotes: 0

JohnyL
JohnyL

Reputation: 7122

Assuming it all happens in the form:

Private Sub txtMain_AfterUpdate()
  Dim x As Integer
  Dim txt As MSForms.TextBox
  For x = 2 To 6
    If Controls("txt" & x).Text <> "" Then
      Controls("txt" & (x - 1)).SetFocus
    End If
  Next
End Sub

The idea is to iterate through all textboxes assuming they have same pattern in the name: "txt" + number. The loop starts with second text box because there's no previous textbox before first one.

UPDATE

Private Sub txtMain_AfterUpdate()
  Dim ctrl As MSForms.Control
  Dim txt As MSForms.TextBox
  Dim txtPrev As MSForms.TextBox
  Dim prevTag As String
  For Each ctrl In Me.Controls
    If TypeOf ctrl Is MSForms.TextBox Then
      Set txt = ctrl
      '// Somehow get the value of tag
      prevTag = "GET TAG VALUE OF PREVIOUS TEXTBOX"
      If txt.Text <> "" Then
        Set txtPrev = FindPreviousTextBox(prevTag)
        If Not txtPrev Is Nothing Then
          txtPrev.SetFocus
          Exit For
        End If
      End If
    End If
  Next
End Sub

Private Function FindPreviousTextBox(prevTag As String) As MSForms.TextBox
  Dim ctrl As MSForms.Control
  For Each ctrl In Me.Controls
    If TypeOf ctrl Is MSForms.TextBox Then
      If ctrl.Tag = prevTag Then
        Set FindPreviousTextBox = ctrl
        Exit Function
      End If
    End If
  Next
End Function

If you want to manipulate with tags, you first need to iterate over all controls and check for textboxes. If a control is textbox, then you get the Tag property from it. The only thing is to somehow get the tag value of previous textbox (since I don't know how you do it). I wrote in the comment that you need to do it somehow.

Upvotes: 0

Related Questions