浏览用户窗体 Excel VBA 上的记录

Navigating through records on UserForm Excel VBA

提问by CaptainABC

I have created a userform in Excel VBA, which I will use to view and update certain records from the sheet I have:

我在 Excel VBA 中创建了一个用户表单，我将使用它来查看和更新​​我拥有的工作表中的某些记录：

UserForm1 http://im35.gulfup.com/Ay43Z.png

UserForm1 http://im35.gulfup.com/Ay43Z.png

What I can't seem to figure out is how to make it Navigate between the records in the Sheet which have "Y" in the Status column.

我似乎无法弄清楚如何让它在“状态”列中有“Y”的工作表中的记录之间导航。

Sheet1 http://im36.gulfup.com/cq6CN.png

Sheet1 http://im36.gulfup.com/cq6CN.png

I need the user to be able to edit the Comments column through the UserForm for the records he is viewing so that the changes are saved in the Sheet. The rest of the textboxes on the Userform are set to Locked so that they can only display data.

我需要用户能够通过 UserForm 编辑他正在查看的记录的 Comments 列，以便将更改保存在工作表中。用户窗体上的其余文本框设置为锁定，以便它们只能显示数据。

The problem I'm facing now is that I can only seem to be able to loop between all the records. What I need is to navigate between the ones having "Y" as their "Status". Also, I can't seem to figure out how have the changes made in the Comments box on the form to be saved on the sheet.

我现在面临的问题是我似乎只能在所有记录之间循环。我需要的是在“Y”作为“状态”的那些之间导航。此外，我似乎无法弄清楚如何将表单上的“注释”框中所做的更改保存在工作表上。

Any help would be really appreciated!

任何帮助将非常感激！

EDIT:

编辑：

The below is the code I have for the Previous Button:

以下是我为上一个按钮编写的代码：

If CurRecord = 0 Then CurRecord = 1

    With ws
        For i = 1 To (CurRecord - 1)
            If Not .Range("G" & i).Value = "X" Then
                TextBox1.Text = .Range("A" & i).Value
                TextBox2.Text = .Range("B" & i).Value
                '
                '~~> And So on load the rest
                '

                CurRecord = i
                Exit Sub
            End If
        Next i

        If (i - 1) = lRow Then
            MsgBox "End of record reached"
        End If
    End With

When I use this it keeps jumping to the first record.

当我使用它时，它会一直跳到第一条记录。

I also tried:

我也试过：

For i = 1 To (CurRecord - 1)

For i = 1 To (CurRecord - 1)

But this gives me an "Out of Range" error when it reaches the first record.

但是当它到达第一条记录时，这给了我一个“超出范围”的错误。

Wonder where I am going wrong?

想知道我哪里出错了吗？

回答by Siddharth Rout

Here is a very basic example of the Nextbutton. Now I am sure you will be able to code the PreviousButton similarly :)

这是一个非常基本的Next按钮示例。现在我相信您将能够以Previous类似方式对Button进行编码:)

Dim ws As Worksheet
Dim lRow As Long
Dim CurRecord As Long

'~~> Load the first record
Private Sub UserForm_Initialize()
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        lRow = .Range("G" & .Rows.Count).End(xlUp).Row

        For i = 2 To lRow
            If .Range("G" & i).Value = "Y" Then
                TextBox1.Text = .Range("A" & i).Value
                TextBox2.Text = .Range("B" & i).Value
                '
                '~~> And So on load the rest
                '

                CurRecord = i
                Exit For
            End If
        Next i
    End With
End Sub

'~~> Next Button
Private Sub CommandButton3_Click()
    If CurRecord = 0 Then CurRecord = 1

    With ws
        For i = (CurRecord + 1) To lRow
            If .Range("G" & i).Value = "Y" Then
                TextBox1.Text = .Range("A" & i).Value
                TextBox2.Text = .Range("B" & i).Value
                '
                '~~> And So on load the rest
                '

                CurRecord = i
                Exit Sub
            End If
        Next i

        If (i - 1) = lRow Then
            MsgBox "End of record reached"
        End If
    End With
End Sub

Followup from comments

来自评论的跟进

Try this (Untested)

试试这个（未经测试）

'~~> Previous Button
Private Sub CommandButton2_Click()
    If CurRecord = 2 Then
        MsgBox "Begining of record reached"
        Exit Sub
    ElseIf CurRecord = 0 Then
        CurRecord = 3
    End If

    With ws
        For i = (CurRecord - 1) To 2 Step -1
            If .Range("G" & i).Value = "Y" Then
                TextBox1.Text = .Range("A" & i).Value
                TextBox2.Text = .Range("B" & i).Value
                '
                '~~> And So on load the rest
                '

                CurRecord = i
                Exit Sub
            End If
        Next i

        If i = 2 Then
            MsgBox "Begining of record reached"
        End If
    End With
End Sub

回答by Floris

If you auto filter on "Y", then the navigation will only visit the rows that are visible (have the "Y" in them). Simply:

如果您自动筛选“Y”，则导航将仅访问可见的行（其中包含“Y”）。简单地：

ActiveSheet.Range("$A1:$G").AutoFilter Field:=7, Criteria1:="Y"