Generate auto increment number from VB form

J

john_t_h

I am trying to design a simple task management spreadsheet where users
can insert new tasks via a VB form.

What I want to do is have the form auto insert an incremented number (I
want to be able to specifiy the first number) into the first cell. This
number will be the task ID number.

Any ideas?

Here is the code I have so far to insert the new row


Code:
--------------------
Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Task_List")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'copy the data to the database
'auto increment number

' descrption
ws.Cells(iRow, 2).Value = Me.TextBox1.Value
' priority
ws.Cells(iRow, 3).Value = Me.ComboBox1.Value
' start date
ws.Cells(iRow, 4).Value = Me.TextBox2.Value
' due date
ws.Cells(iRow, 5).Value = Me.TextBox3.Value
' status
ws.Cells(iRow, 6).Value = Me.ComboBox3.Value
' category
ws.Cells(iRow, 7).Value = Me.ComboBox2.Value

'clear the data
Me.TextBox1.Value = ""
Me.ComboBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.ComboBox3.Value = ""
Me.ComboBox2.Value = ""
Me.TextBox1.SetFocus

End Sub
 
R

Rowan

Maybe something like:

If iRow = 2 then 'first row of data
ws.Cells(iRow, 1).Value = 1000 'specified start value
else
ws.Cells(iRow, 1).Value = ws.Cells(iRow-1, 1).Value
End If

Hope this helps
Rowan
 
R

Rowan

sorry should read

If iRow = 2 then 'first row of data
ws.Cells(iRow, 1).Value = 1000 'specified start value
else
ws.Cells(iRow, 1).Value = ws.Cells(iRow-1, 1).Value + 1
End If

Regards
Rowan
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top