时间戳

首页 > 解决方案 > 关闭用户窗体后如何继续excel工作表中唯一数字的序列？

excel - 关闭用户窗体后如何继续excel工作表中唯一数字的序列？

问题描述

当用户窗体关闭并稍后打开时，我在获取唯一编号（序列号）的序列时遇到问题。首先，当我在用户表单中填写数据时，所有内容都以正确的顺序完美地记录在 Excel 表中；如果我关闭用户表单并通过用新数据填充用户表单来运行代码，则唯一 ID 再次从“1”开始，但不是根据先前保存的 excel 工作表行号。

IMG1

下面是我试过的代码：

Private Sub cmdSubmit_Click()
    Dim WB As Workbook
    Dim lr As Long

    Set WB = Workbooks.Open("C:\Users\Desktop\Book2.xlsx")

    Dim Database As Worksheet
    Set Database = WB.Worksheets("Sheet1")
    eRow = Database.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

    lr = Database.Range("a65536").End(xlUp).Row
    With Sheets("Sheet1")
        If IsEmpty(.Range("A1")) Then
            .Range("A1").Value = 0
        Else
            Database.Cells(lr + 1, 1) = Val(Database.Cells(lr, 1)) + 1
        End If
    End With

    Database.Cells(eRow, 4).Value = cmbls.Text
    Database.Cells(eRow, 2).Value = txtProject.Text
    Database.Cells(eRow, 3).Value = txtEovia.Text
    Database.Cells(eRow, 1).Value = txtUid.Text

    Call UserForm_Initialize
    WB.SaveAs ("C:\Users\Desktop\Book2.xlsx")

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim maxNumber

    If Not Intersect(Target, Range("B:B")) Is Nothing Then
        ' don't run when more than one row is changed
        If Target.Rows.Count > 1 Then Exit Sub

        ' if column A in the current row has a value, don't run
        If Cells(Target.Row, 1) > 0 Then Exit Sub

        ' get the highest number in column A, then add 1 and write to the
        ' current row, column A
        maxNumber = Application.WorksheetFunction.Max(Range("A:A"))
        Target.Offset(0, -1) = maxNumber + 1
    End If

End Sub

Private Sub UserForm_Initialize()
    With txtUid
        .Value = Format(Val(Cells(Rows.Count, 1).End(xlUp)) + 1, "0000")
        .Enabled = False
    End With
    With txtProject
        .Value = ""
        .SetFocus
    End With
End Sub

在此图像中，如果您看到唯一 ID 重复 1 和 2，但我需要 1、2、3、4....

IMG2

标签： excelvbauserform

解决方案

我认为这就是问题的来源。每次用户表单为 时，您都需要重新计算最后一行Initialized。

Private Sub UserForm_Initialize()

Dim ws as Worksheet: Set ws = Thisworkbook.Sheets("Database")
    With txtUid
        .Value = Format(ws.Range("A" & ws.Rows.Count).End(xlUp) + 1, "0000")
        .Enabled = False
    End With

    With txtProject
        .Value = ""
        .SetFocus
    End With
End Sub

推荐阅读