How to make excel form automatically create a new number for each new row

Question

How to make excel form automatically create a new number for each new row

Anonymous

Hi All - Need help!

Just wondering if it is possible to add a code that will allow a data entry from to add a new number (sequential from the last) each time it is added.

I am making an incident register, but personnel that need to use it aren't very good with excel - so I need everything automated.

I have attached a picture of the form and the spreadsheet it is to go into - the code for the form is rather long, but adds everything as it should, but I need a column (A) in front of 'type' that will automatically assign a number in there each time the data is added by clicking the save button.

I have tried to add the column and numbers before, but because my form is looking for the next blank row, it wont work.

When the 'OK' button is clicked, the worksheet is looking for the next empty row and adding the boxes I need. but I just can't work out how to have a number system on the form or in the sheet that will start with No. 1 and continue down each time the OK button is pressed to add the data to the sheet

Is there a form field maybe, that I am missing.... going nuts!

Private Sub OKButton_Click()

Dim emptyRow As Long

'Make Incident Register active

Sheet3.Activate

'Determine emptyRow

emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Transfer information

Cells(emptyRow, 1).Value = TypeComboBox.Value

Cells(emptyRow, 2).Value = DepartmentComboBox.Value

Cells(emptyRow, 3).Value = DateTextBox1.Value

Cells(emptyRow, 4).Value = TimeTextBox2.Value

Cells(emptyRow, 5).Value = DateRecordedTextBox.Value

Cells(emptyRow, 6).Value = ReportedTextBox.Value

Cells(emptyRow, 7).Value = PlantTextBox.Value

Cells(emptyRow, 8).Value = InvolvedTextBox.Value

Cells(emptyRow, 9).Value = DescriptionTextBox3.Value

Cells(emptyRow, 10).Value = ActionsTextBox.Value

Cells(emptyRow, 11).Value = SupervisorTextBox.Value

Cells(emptyRow, 12).Value = CauseComboBox.Value

Cells(emptyRow, 13).Value = RiskComboBox.Value

IncidentReport.Hide

Hope this makes sense!

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

1 additional answer

Answer 1

emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

Try

emptyRow = Range("A" & Rows.Count).End(xlUp).Offset(1).Row

BTW, do you know that you can use the TAG property of the controls to store informations, e.g. the column? Sample: TypeComboBox.Tag = "A"

After that you can use a loop and load/save all informations in one step with a few lines:

for each C in Me.Controls

if C.Tag <> "" then Range(C.Tag & emptyRow) = C

Answer 2

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more

Share via

How to make excel form automatically create a new number for each new row

1 additional answer