Excel用户窗体下一个按钮转到下一行单元格

Question

我在 3x 列中有一组数据：姓名、年龄、工作。

我整理了一个用户表单，用户可以在其中查找姓名、年龄和工作，在 3x 个单独的文本框(文本框 1、文本框 2 和文本框 3(中。它从顶部开始，这是通过设计完成的，效果很好。我还有一个"下一步"按钮，当用户单击下一步时，它将转到列表中的下一项。

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

法典：

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

这在理论上应该有效，但事实并非如此。我不确定我哪里出错了。有什么建议我哪里出错了吗？

谢谢

修改后的代码：

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

Answer 1

详细说明我的评论，如下所示：

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

---

如果您按照组合框的路线获取值，则可以在 userform_initialize(( 期间使用以下方法设置 combobox 数组：

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

Answer 2

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

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