使用 VBA 中的下一个和上一个按钮在 Excel 中加载用户表单给定数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28053117/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using Next and Previous Buttons in VBA to Load Userform Given Data in Excel
提问by Tyler
I am working on a userform in excel-VBA. I am trying to use a next and previous button to cycle through previous entries in a table. The previous button should select the previous row and load it into the userform. The next button should select the next row and load it into the userform as well.The data table has no set length. Below is my attempt at this. I know I have a disconnect between the two buttons function and am not sure how to connect them. Any help would be greatly appreiciated. I will respond as quickly as I can.
我正在 excel-VBA 中处理用户表单。我正在尝试使用下一个和上一个按钮来循环浏览表格中的前一个条目。上一个按钮应该选择上一行并将其加载到用户表单中。下一个按钮应该选择下一行并将其加载到用户表单中。数据表没有设置长度。下面是我在这方面的尝试。我知道我在两个按钮功能之间断开了连接，并且不确定如何连接它们。任何帮助将不胜感激。我会尽快回复。
Public PrevRow1 As Long
Public cnt1 As Long
Public cnt2 As Long
'Prev Button
Private Sub CommandButton4_Click()
cnt1 = cnt1 + 1
Sheet4.Activate
PrevRow1 = WorksheetFunction.CountA(Range("A:A")) + 1 - cnt1 + cnt2
ComboBox1.Value = Cells(PrevRow1, 1)
ComboBox10.Value = Cells(PrevRow1, 2)
ComboBox2.Value = Cells(PrevRow1, 3)
ComboBox3.Value = Cells(PrevRow1, 4)
ComboBox4.Value = Cells(PrevRow1, 5)
TextBox6.Value = Cells(PrevRow1, 6)
TextBox2.Value = Cells(PrevRow1, 7)
TextBox3.Value = Cells(PrevRow1, 8)
TextBox4.Value = Cells(PrevRow1, 10)
ComboBox6.Value = Cells(PrevRow1, 11)
ComboBox7.Value = Cells(PrevRow1, 12)
ComboBox8.Value = Cells(PrevRow1, 13)
ComboBox9.Value = Cells(PrevRow1, 14)
TextBox5.Value = Cells(PrevRow1, 15)
If PrevRow1 = 1 Then
MsgBox "This is the last entry.", , "Alert!"
CommandButton4.Enabled = False
End If
Me.Label15.Caption = PrevRow1 - 1 & " " & "of" & " " & WorksheetFunction.CountA(Range("A:A")) - 1
End Sub
'Next button
Private Sub CommandButton5_Click()
Dim i As Long
cnt2 = cnt2 + 1
Dim NextRow1 As Long
Sheet4.Activate
NextRow1 = PrevRow1 - (cnt1 - cnt2) + 3
ComboBox1.Value = Cells(NextRow1, 1)
ComboBox10.Value = Cells(NextRow1, 2)
ComboBox2.Value = Cells(NextRow1, 3)
ComboBox3.Value = Cells(NextRow1, 4)
ComboBox4.Value = Cells(NextRow1, 5)
TextBox6.Value = Cells(NextRow1, 6)
TextBox2.Value = Cells(NextRow1, 7)
TextBox3.Value = Cells(NextRow1, 8)
TextBox4.Value = Cells(NextRow1, 10)
ComboBox6.Value = Cells(NextRow1, 11)
ComboBox7.Value = Cells(NextRow1, 12)
ComboBox8.Value = Cells(NextRow1, 13)
ComboBox9.Value = Cells(NextRow1, 14)
TextBox5.Value = Cells(NextRow1, 15)
Me.Label15.Caption = NextRow1 - 1 & " " & "of" & " " & WorksheetFunction.CountA(Range("A:A")) - 1
End Sub
New Code
新代码
Public nCurrentRow As Long
Sub UserForm_Initialize()
nCurrentRow = Sheets(4).Cells(Rows.Count, 1).End(xlUp).Row
TraverseData (nCurrentRow)
end sub
'Prev button
Private Sub CommandButton14_Click()
Do
nCurrentRow = nCurrentRow - 1
TraverseData (nCurrentRow)
Loop Until nCurrentRow = 1 Or Sheets(4).Cells(nCurrentRow, 1).Value = Me.ComboBox12.Value
End Sub
'Next Button
Private Sub CommandButton15_Click()
Do
nCurrentRow = nCurrentRow + 1
TraverseData (nCurrentRow)
Loop Until Sheets(4).Cells(nCurrentRow, 1).Value = "" Or Sheets(4).Cells(nCurrentRow, 1).Value = Me.ComboBox12.Value
End Sub
Private Sub TraverseData(nRow As Long)
Me.ComboBox1.Value = Sheets(4).Cells(nRow, 1)
Me.ComboBox10.Value = Sheets(4).Cells(nRow, 2)
Me.ComboBox2.Value = Sheets(4).Cells(nRow, 3)
Me.ComboBox3.Value = Sheets(4).Cells(nRow, 4)
Me.ComboBox4.Value = Sheets(4).Cells(nRow, 5)
Me.TextBox6.Text = Sheets(4).Cells(nRow, 6)
Me.TextBox2.Text = Sheets(4).Cells(nRow, 7)
Me.TextBox3.Value = Sheets(4).Cells(nRow, 8)
Me.TextBox4.Value = Sheets(4).Cells(nRow, 10)
Me.ComboBox6.Value = Sheets(4).Cells(nRow, 11)
Me.ComboBox7.Value = Sheets(4).Cells(nRow, 12)
Me.ComboBox8.Value = Sheets(4).Cells(nRow, 13)
Me.ComboBox9.Value = Sheets(4).Cells(nRow, 14)
Me.TextBox5.Value = Sheets(4).Cells(nRow, 15)
End Sub
回答by user3561813
I'm having a difficult time determining what cnt1 andcnt2 refer to.
我很难确定cnt1 andcnt2 指的是什么。
In my opinion, you only need one publicly declared variable used to reference the row of data that is loaded into the table. That variable should be set once the userform is loaded based on desired behavior. For example, the userform loads with data from the last row.
在我看来，您只需要一个公开声明的变量来引用加载到表中的数据行。一旦根据所需的行为加载用户表单，就应该设置该变量。例如，用户表单加载了最后一行的数据。
Check the below code as a basis and let me know if it helps at all:
检查以下代码作为基础，让我知道它是否有帮助：
Public nCurrentRow As Long
Private Sub cmdNext_Click()
Do
nCurrentRow = nCurrentRow + 1
TraverseData (nCurrentRow)
Loop Until Sheet1.Cells(nCurrentRow, 1).Value = "" Or Sheet1.Cells(nCurrentRow, 1).Value = Me.ComboBox1.Value
End Sub
Private Sub cmdPrev_Click()
Do
nCurrentRow = nCurrentRow - 1
TraverseData (nCurrentRow)
Loop Until nCurrentRow = 1 Or Sheet1.Cells(nCurrentRow, 1).Value = Me.ComboBox1.Value
End Sub
Private Sub UserForm_Initialize()
nCurrentRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
TraverseData (nCurrentRow)
End Sub
Private Sub TraverseData(nRow As Long)
Me.TextBox1.Value = Sheet1.Cells(nRow, 1)
Me.TextBox2.Value = Sheet1.Cells(nRow, 2)
Me.TextBox3.Value = Sheet1.Cells(nRow, 3)
End Sub
Needless to say, it must be amended to handle each of your individual userform controls. Additionally, reuse your error checking to confirm that it doesn't traverse into negative rows (you've already done this in your version of the code).
不用说，必须对其进行修改以处理每个单独的用户窗体控件。此外，重用您的错误检查以确认它不会遍历到负行（您已经在您的代码版本中完成了此操作）。
Notice I encapsulated the `TraverseData into its own SubRoutine so you don't have to rewrite it for each button. It really cleans up the data.
请注意，我将 `TraverseData 封装到它自己的子程序中，因此您不必为每个按钮重写它。它确实清理了数据。
回答by blindguy
Public ViewRow as long
'Prev Button
Private Sub CommandButton5_Click()
if ViewRow > 1 then 'assumes row 1 is the first row
ViewRow = ViewRow - 1
CommandButton5.Enabled = True
if ViewRow = 1 then
MsgBox "This is the first entry.", , "Alert!"
CommandButton4.Enabled = False
end if
end if
Sheet4.Activate
ComboBox1.Value = Cells(ViewRow, 1)
...
TextBox5.Value = Cells(ViewRow, 15)
Me.Label15.Caption = ViewRow & " " & "of" & " " & WorksheetFunction.CountA(Range("A:A"))
End Sub
'Next button
Private Sub CommandButton5_Click()
if ViewRow < WorksheetFunction.CountA(Range("A:A")) then
ViewRow = ViewRow + 1
CommandButton4.Enabled = True
if ViewRow = 1 then
MsgBox "This is the last entry.", , "Alert!"
CommandButton5.Enabled = False
end if
end if
Sheet4.Activate
ComboBox1.Value = Cells(NextRow1, 1)
...
TextBox5.Value = Cells(NextRow1, 15)
Me.Label15.Caption = ViewRow & " " & "of" & " " & WorksheetFunction.CountA(Range("A:A"))
End Sub
OR
或者
Public ViewRow as long
'Prev Button
Private Sub CommandButton5_Click()
if ViewRow > 1 then 'assumes row 1 is the first row
ViewRow = ViewRow - 1
CommandButton5.Enabled = True
if ViewRow = 1 then
MsgBox "This is the first entry.", , "Alert!"
CommandButton4.Enabled = False
end if
end if
call populate_row(ViewRow)
End Sub
'Next button
Private Sub CommandButton5_Click()
if ViewRow < WorksheetFunction.CountA(Range("A:A")) then
ViewRow = ViewRow + 1
CommandButton4.Enabled = True
if ViewRow = 1 then
MsgBox "This is the last entry.", , "Alert!"
CommandButton5.Enabled = False
end if
end if
call populate_row(ViewRow)
End Sub
Sub populate_row(thisrow as long)
Sheet4.Activate
ComboBox1.Value = Cells(NextRow1, 1)
...
TextBox5.Value = Cells(NextRow1, 15)
Me.Label15.Caption = ViewRow & " " & "of" & " " & WorksheetFunction.CountA(Range("A:A"))
End Sub
The second option lets you make changes to how the data is added to the form without changing both functions.
第二个选项允许您更改数据添加到表单的方式，而无需更改这两个功能。
