Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generate auto increment number from VB form
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generate auto increment number from VB form
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generate auto increment number from VB form
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generate auto increment number from VB form
Thanks Rowan, works a treat! -- john_t_ ----------------------------------------------------------------------- john_t_h's Profile: http://www.excelforum.com/member.php...nfo&userid=482 View this thread: http://www.excelforum.com/showthread.php?threadid=40201 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Increment Number | Excel Discussion (Misc queries) | |||
How to auto-number (increment) invoices in Excel? | Excel Discussion (Misc queries) | |||
Generate a unique form number | Excel Programming | |||
Generate number NOT using a form | New Users to Excel | |||
Auto-generate controls on form | Excel Programming |