使用上一个和下一个按钮Excel Form VBA进行行导航 [英] Row navigation using previous and next button excel form vba

问题描述

我有一个用于员工数据输入工作的Excel表格.Excel工作表包含员工的详细信息.我希望表单通过上一个和下一个按钮浏览每个记录，并在表单中显示内容.

I have an excel form for employee data entry work. The excel sheet contains details of employees. I want the form to navigate through each record through previous and next button, also displaying the content in the form.

我已经为此编写了代码，但是无法正常工作.有时在到达记录的开始和结束时会给出无效的记录详细信息.帮帮我

I already written the code for this but it is not working properly. It sometimes gives invalid record details while reaching begin and end of the records. Help me

Private Sub UserForm_Initialize()
frmEmpDetails.ComboGender.AddItem "Male", 0
frmEmpDetails.ComboGender.AddItem "Female", 1
counter = ActiveSheet.UsedRange.Rows.Count
temp_counter = counter
lblTmpCount.Caption = temp_counter
End Sub

Private Sub btnNext_Click()
status = 1
lblTmpCount.Caption = temp_counter
If (temp_counter >= counter) Then
    MsgBox "Reached end"
Else
    temp_counter = temp_counter + 1
    txtID.Text = Cells(temp_counter, 1).Value
    txtName.Text = Cells(temp_counter, 2).Value
    txtDOB.Text = Cells(temp_counter, 3).Value
    ComboGender.Value = Cells(temp_counter, 4).Value
    txtAboutEmp.Text = Cells(temp_counter, 5).Value
    lblTmpCount.Caption = temp_counter
End If
End Sub

Private Sub btnPrev_Click()
status = 1
lblTmpCount.Caption = temp_counter
If (temp_counter < 2) Then
    MsgBox "Reached beginning"
Else
    txtID.Text = Cells(temp_counter, 1).Value
    txtName.Text = Cells(temp_counter, 2).Value
    txtDOB.Text = Cells(temp_counter, 3).Value
    ComboGender.Value = Cells(temp_counter, 4).Value
    txtAboutEmp.Text = Cells(temp_counter, 5).Value
temp_counter = temp_counter - 1
End If
End Sub

推荐答案

我建议您使用子例程获取和放置数据，然后使用导航按钮更改行.测试您的GetData和PutData例程，以确保它们执行了预期的操作.

I suggest you use subroutines to Get and Put data, and then use your navigation buttons to change the row. Test your GetData and PutData routines to make sure they do what is expected of them.

这是我的一个项目的示例，该示例使用带有文本框和单元格引用的类似控件.在我的示例中，我从第4行开始数据.大多数变量是在激活表单后声明的.

Here is an example from a project of mine using similar controls with your textboxes and cell references. In my example, I started the data on Row 4.. Most of the variables are declared upon activation of the form.

Private Sub cmdFirst_Click()   
    R = 4
    Call GetData         
End Sub

Private Sub cmdPrev_Click()
    If R = 4 Then
        MsgBox ("Already on First Record")
    Else
        R = R - 1
        Call GetData        
    End If            
End Sub

Private Sub cmdNext_Click()
    lastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).row   
    If R = lastRow Then
        MsgBox ("Already on the last record.")
    Else
        R = R + 1
        Call GetData        
    End If   
End Sub

Private Sub cmdLast_Click()
    R = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).row
    Call GetData
End Sub

Private Sub cmdNew_Click()
    R = (Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).row) + 1
    Call SetID
    Call ClearData
    txtID = newID
End Sub

Private Function SetID()  
Dim rng As Range
    Set rng = Sheets("Sheet1").Range("a4", Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).row

    newID = Application.WorksheetFunction.Max(rng) + 1

End Function

Private Sub GetData()
    txtID.Text = Sheets("Sheet1").Cells(R, 1).Value
    txtName.Text = Sheets("Sheet1").Cells(R, 2).Value
    txtDOB.Text = Sheets("Sheet1").Cells(R, 3).Value
    ComboGender.Value = Sheets("Sheet1").Cells(R, 4).Value
    txtAboutEmp.Text = Sheets("Sheet1").Cells(R, 5).Value
End Sub

Private Sub ClearData()
    txtName.Text = ""
    txtDOB.Text = ""
    ComboGender.Value = ""
    txtAboutEmp.Text = ""
End Sub

Private Sub PutData()
    Sheets("Sheet1").Cells(R, 1).Value = txtID.Text
    Sheets("Sheet1").Cells(R, 2).Value = txtName.Text
    Sheets("Sheet1").Cells(R, 3).Value = txtDOB.Text 
    Sheets("Sheet1").Cells(R, 4).Value = ComboGender.Value
    Sheets("Sheet1").Cells(R, 5).Value = txtAboutEmp.Text
End Sub

这篇关于使用上一个和下一个按钮Excel Form VBA进行行导航的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！