Search code examples
excelvbauserform

How to continue the sequence of the unique numbers in the excel sheet after closing the userform?

I am facing a problem in getting the sequence of the unique numbers(Serial number) when the userform is closed and opened later on. Firstly, when I fill the data in the userform everything is captured in the excel sheet perfectly with correct sequence; if I close the userform and run the code by filling the userform with new data the unique ID's are again starting from "1" but not according to the excel sheet row number which was previously saved.

IMG1

Below is the code I tried:

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

In this image if you see unique id's are repeating 1 and 2, but I need as 1,2,3,4....

IMG2

Solution

  • I think this is where the issue is coming from. You need to re-calculate the last row every time the user form is 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