.SelStart/.SelLength Not Working on Multi-Step UserForm

[mharper90]

I have a UserForm that mimics what you would see in a "CTRL+F" type window. It runs through the data sheet, row by row, and allows rapid entry of a particular data value, so I want the TextBox that holds that data on the UserForm to auto select the previous data or set focus there to allow entry if the data is blank. I've gotten it to work on the first row when the UserForm first opens, but it doesn't work when nearly the same code is used to generate the next row of data when the "NEXT" Command Button is clicked. Anyone have any ideas?

Also, a bonus question...Can anyone help me with using the down arrow key to perform the same function as clicking the "NEXT" Command Button? This way it should be easy to type the data value, press down arrow, type the data value, press down arrow, etc.

Private Sub UserForm_Activate()

LoadUF18

End Sub


Private Sub CommandButton5_Click()  'Next Button

Next1

End Sub

Sub LoadUF18()

Dim ws1     As Worksheet: Set ws1 = ThisWorkbook.Sheets("Main Data")
Dim c     As Range
Dim d     As Range
Dim Lr     As Long

Lr = ws1.Range("A" & Rows.Count).End(xlUp).Row

UserForm18.Label18 = "Period  " & UserForm10.ComboBox1.Value


'counts total number of valid rows
y = 0

For Each d in ws1.Range("T7:T" & Lr)
     If d.Value = "" Or d.Value = INACTIVE P" & UserForm10.ComboBox1.Value Then
          y = y + 1
          UserForm18.TextBox10.Value = y
     End If

Next d


'displays running count of # of total#
If y > 0 Then
     UserForm18.TextBox9.Value = 1
     z = UserForm18.TextBox9.Value
     UserForm18.Label16 = z & "  of  " & y


'loads data in userform
     For Each c In ws1.Range("T7:T" & Lr)
          If c.Value = "" Or c.Value = INACTIVE P" & UserForm10.ComboBox1.Value Then
               Me.TextBox2.Value = c.Offset(0, -18).Value
               Me.TextBox3.Value = c.Offset(0, -17).Value
               If c.Offset(0, -12 + UserForm10.ComboBox1.Value).Interior.Color = RGB(255, 255, 0) Then
                    With Me.TextBox8
                         .Value = c.Offset(0, -12 + UserForm10.ComboBox1.Value).Value
                         .SelStart = 0
                         .SelLength = Len(.Value)
                    End With
               Else
                    With Me.TextBox11
                         .Value = c.Offset(0, -12 + UserForm10.ComboBox1.Value).Value
                         .SelStart = 0
                         .SelLength = Len(.Value)
                    End With
               End If
               If c.Offset(0, -12 + UserForm10.ComboBox1.Value).Value <> "" And c.Offset(0, -12 + UserForm10.ComboBox1.Value).Interior.Color <> RGB(255, 255, 0) Then
                    Me.TextBox8.Value = "-->"
               End If
               If c.Offset(0, -19).Value = "NO" Or c.Offset(0, 4).Value = "NO" Then
                    Me.TextBox8.Enabled = False
                    Me.TextBox8.Value = "N/A"
               Else
                    Me.TextBox8.Enabled = True
               End If
               c.Offset(0, -18).Select
               Exit Sub
          End If
     Next c
End If

End Sub

Sub Next1()

Dim ws1     As Worksheet: Set ws1 = ThisWorkbook.Sheets("Main Data")
Dim c     As Range
Dim d     As Range
Dim Lr     As Long

Lr = ws1.Range("A" & Rows.Count).End(xlUp).Row


'places data from previous page into the worksheet
x = Selection.Row

If Me.TextBox8.Value <> "" And Me.TextBox8.Value <> "N/A" And Me.TextBox8.Value <> "--->" And Me.TextBox11.Value = "" Then
     With ws1.Range("B" & x).Offset(0, 6 + UserForm10.ComboBox1.Value)
          .Value = Me.TextBox8.Value
          .Interior.Color = RGB(255, 255, 0)
     End With
End If
If Me.TextBox11.Value <> "" Then
     With ws1.Range("B" & x).Offset(0, 6 + UserForm10.ComboBox1.Value)
          .Value = Me.TextBox11.Value
          .Interior.Color = xlNone
     End With
End If


'unloads textboxes
Me.TextBox8.Value = ""
Me.TextBox11.Value = ""


'adds to the # of total# display
x = x + 1


'prevents advancing if last row is reached
If x > Lr Then
     x = Lr
     Exit Sub
End If


'updates # of total#
z = UserForm18.TextBox9.Value
y = UserForm18.TextBox10.Value


'loads data into userform
For Each c In ws1.Range("T" & x & ":T" & Lr)
     If c.Value = "" Or c.Value = INACTIVE P" & UserForm10.ComboBox1.Value Then
          Me.TextBox2.Value = c.Offset(0, -18).Value
          Me.TextBox3.Value = c.Offset(0, -17).Value
          If c.Offset(0, -12 + UserForm10.ComboBox1.Value).Interior.Color = RGB(255, 255, 0) Then
               With Me.TextBox8
                    .Value = c.Offset(0, -12 + UserForm10.ComboBox1.Value).Value
                    .SelStart = 0
                    .SelLength = Len(.Value)
               End With
          Else
               With Me.TextBox11
                    .Value = c.Offset(0, -12 + UserForm10.ComboBox1.Value).Value
                    .SelStart = 0
                    .SelLength = Len(.Value)
               End With
          End If
          If c.Offset(0, -12 + UserForm10.ComboBox1.Value).Value <> "" And c.Offset(0, -12 + UserForm10.ComboBox1.Value).Interior.Color <> RGB(255, 255, 0) Then
               Me.TextBox8.Value = "-->"
          End If
          If c.Offset(0, -19).Value = "NO" Or c.Offset(0, 4).Value = "NO" Then
               Me.TextBox8.Enabled = False
               Me.TextBox8.Value = "N/A"
          Else
               Me.TextBox8.Enabled = True
          End If
          c.Offset(0, -18).Select
          z = z + 1
          UserForm18.TextBox9.Value = z
          UserForm18.Label16 = z & "  of  " & y
          Exit Sub
     End If
Next c


End Sub

 

[sijpie]

You can only seselect text in a field that has the focus, so in the Next1() sub:

               With Me.TextBox8
                    .Value = c.Offset(0, -12 + UserForm10.ComboBox1.Value).Value
                    .SetFocus
                    .SelStart = 0
                    .SelLength = Len(.Value)
               End With
          Else
               With Me.TextBox11
                    .Value = c.Offset(0, -12 + UserForm10.ComboBox1.Value).Value
                    .SetFocus
                    .SelStart = 0
                    .SelLength = Len(.Value)
               End With

 

[sijpie]

The Arrow key as accelerator is perhaps too complicated, but how about Alt-N?

To get that workin, go to the userform in the VBA editor, click once on the Next button. Now look at the properties table in the lower left corner. The first property is Accelerator. You can enter a letter here, but only one of the letters that appear in the button caption. So if the button caption is "Next", then you can use any of these four letters. I suggest the N or the X.

Now try it in the userform. You will see that the chosen letter is underlined on the button.

To use it press Alt-N (or Alt-X, whichever you selected)