VBA code to auto generate the next sequential number?

pgtaylor

New Member
Joined
Sep 19, 2016
Messages
3
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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.

Code:
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,
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top