Generate auto increment number from VB form

[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

Please Login or Register  to view this content.

[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

"john_t_h" wrote:

>
> 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
> --------------------
>
>
> --
> john_t_h
> ------------------------------------------------------------------------
> john_t_h's Profile: http://www.excelforum.com/member.php...fo&userid=4826
> View this thread: http://www.excelforum.com/showthread...hreadid=402011
>
>

[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

"Rowan" wrote:

> 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
>
> "john_t_h" wrote:
>
> >
> > 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
> > --------------------
> >
> >
> > --
> > john_t_h
> > ------------------------------------------------------------------------
> > john_t_h's Profile: http://www.excelforum.com/member.php...fo&userid=4826
> > View this thread: http://www.excelforum.com/showthread...hreadid=402011
> >
> >

[john_t_h]

Thanks Rowan, works a treat!!