VBA code to auto generate the next sequential number?

[pgtaylor]

Hi Mr Excel Guru’s
Looking for a helping hand to finish a database I started for my wife.
My wife volunteers at a dog shelter, and the process for managing the dogs is by manually hand crunching in a book. So she asked if I could help out by creating a database for her.
Due to having some free time I decided to have a bash doing it in excel VBA.
I have a workbook with 3 worksheets named “Home” “Data” and “Print”
The Home sheet comprises of 2 command buttons named “Enter Dog Details” and “Close”. On clicking the “Enter Dog Details” button it opens up the form template.
The form template contains 3 frames. The first frame has two “ComboBoxes” with a command button to Search by “Unique Number” and “Name” fields.
The second frame has thirteen text boxes and labels for data to be entered.
The third frame has 5 command buttons named “New” “Save” “Delete” “Export to Pdf” and “Close”.
The “New” command button currently has code for data to be entered to the textboxes manually, and includes the next sequential number too. What I was hoping can be done is additional code can be used to auto generate the next sequential number, via the "New" command button, but can the generated number be populated in the “Unique Number” textbox?
It would be excellent if it can!
Any pointers would be greatly appreciated and would please my wife no end.
Many many thanks
Peter

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

I think this should work.

It uses the Worksheet MAX command in VBA to find the highest number in column A. It then adds one to that and displays the new number in TextBox1. It does that both when the UserForm is Initialized and after the CommandButton is pushed. It writes the old number to the next available row on the Worksheet at the same time.

I uses a UserForm called Userorm1 with TextBox1 and CommandButton1 Objects on it.

This code needs to be pasted into the place where UserForm code gnormally goes.

Private Sub CommandButton1_Click()
    With ThisWorkbook.Worksheets("Sheet1")
        .Cells(.Rows.Count, "A").End(xlUp).Offset(1) = TextBox1.Value
        TextBox1.Text = Application.Max(.Range("A:A")) + 1
    End With
End Sub

Private Sub UserForm_Initialize()
    With ThisWorkbook.Worksheets("Sheet1")
        TextBox1.Text = Application.Max(.Range("A:A")) + 1
    End With
End Sub

Regards,

 

[pgtaylor]

Many thanks Rick, exactly what I wanted ...... Cheers!

 

[RickXL]

No problem - glad to be of assistance.

Regards,

 

[CampbellC]

just what i needed thanks