Excel Userform下一个按钮转到下一行单元格

I have a set of data in 3x columns: Name, Age, Job.

我在3x列中有一组数据:名称,年龄,作业。

I have put together a userform where a user can use to find Names, Ages and Jobs, in 3x separate textboxes (TextBox1, TextBox2 and TextBox3). It starts right at the top, which is done by design, which works fine. I also have a 'next' button, where when a user would click next and it will go to the next item in the list.

我已经将用户可以用来在3个单独的文本框(TextBox1,TextBox2和TextBox3)中查找名称,年龄和作业的用户表单放在一起。它从顶部开始,由设计完成,工作正常。我还有一个“下一个”按钮,当用户点击下一个按钮时,它将转到列表中的下一个项目。

So when the user clicks next, it should ideally go to row 3 data (row 1 = headers).

因此,当用户单击下一步时,理想情况下应该转到第3行数据(第1行=标题)。

Code:

Private Sub CommandButton3_Click()
    Dim AANo As String
    Dim AANa As String
    Dim AAEm As String
    Dim NextLR As Long

    NextLR = Sheets("AASD").Cells(Rows.count, "QH").End(xlUp).row

    For i = 2 To NextLR
        With Worksheets("AASD")
            Name = .Range(i, 8).Value
            Age = .Range(i, 9).Value
            Job = .Range(i, 10).Value
        End With

        TextBox1.Value = Name
        TextBox2.Value = Age
        TextBox3.Value = Job
    Next i

End Sub

This in theory should work, but it doesn't. I am not sure where I went wrong. Any advise where I went wrong?

这在理论上应该有效,但事实并非如此。我不知道我哪里出错了。有什么建议我哪里出错了?

Thanks

Modified code:

Private Sub CommandButton3_Click()
    Dim AANo As String
    Dim AANa As String
    Dim AAEm As String
    Dim NextLR As Long
    Dim count As Long
    NextLR = Sheets("AASD").Cells(Rows.count, "QH").End(xlUp).row
    count = 2
    For i = count To NextLR
        With Worksheets("AASD")
            AANo = .Range(i, 8).Value
            AAName = .Range(i, 9).Value
            AAEm = .Range(i, 10).Value
        End With

        TextBox1.Value = AANo
        TextBox2.Value = AANa
        TextBox3.Value = AAEm
        count = count + 1
        Exit For

    Next i

End Sub

2 个解决方案

#1

Elaborate on my comment, somethign like:

详细说明我的评论,有些像:

Public k as Long

Private Sub CommandButton3_Click()
    Dim lr as Long
    with sheets("AASD")
        lr = .cells(.rows.count,8).end(xlup).row
        If k > lr OR k <3 then k = 3
        TextBox1.Value = .Cells(k, 8).Value
        TextBox2.Value = .Cells(k, 9).Value
        TextBox3.Value = .Cells(k, 10).Value
    end with
    k = k + 1
End Sub

If you go the route of a combobox for the values, you can set the combobox array during userform_initialize() using:

如果您使用组合框的路径获取值,则可以在userform_initialize()期间使用以下命令设置组合框阵列:

dim arr() as variant, lr as long
with sheets("AASD")
    lr = .cells(.rows.count,8).end(xlup).row
    arr = .range(.cells(3,8),.cells(lr,8)).Value
    UserForm1.ComboBox1.List = arr
end with

#2

Add another text box and keep it invisible and use it to count the number of times next is clicked.

添加另一个文本框并使其不可见,并使用它来计算下次单击的次数。

Private Sub CommandButton3_Click()

    Dim AANo As String
    Dim AANa As String
    Dim AAEm As String
    Dim NextLR As Long
    Dim counter As Long

    NextLR = Sheets("AASD").Cells(Rows.count, "QH").End(xlUp).row

    counter = me.counterBox + 1 'because on first time it will be empty

    With Worksheets("AASD")
        AANo = .Range(counter, 8).Value
        AAName = .Range(counter, 9).Value
        AAEm = .Range(counter, 10).Value
    End With

    TextBox1.Value = AANo
    TextBox2.Value = AANa
    TextBox3.Value = AAEm

    Me.counterBox = Iif(counter = NextLR, 0, counter)

End Sub

#1