PDA

View Full Version : Autonumbering



chaneybran
08-28-2007, 10:44 AM
I am currently working on a spreadsheet application. I am almost complete but I am stumped on performing the following. Maybe someone more knowledgable on VBA could help me.

I have a button which is titled Insert Task ... When the user clicks the insert task a row appears below with 2 cells filled in on XX1 and XX2. A1 contains the autonumbering 1 and A2 contains the text New Task. Now I have managed to code it so it will number ONLY if the user does this is sequence. Unfortunately I have another button "Insert Sub Task" which inserts a row with cells XX2 and XX3. Which contain 1.1 New Subtask or 1.2 New Subtask ... depending on the previously inserted TASK.

Is there any viable way to perform this type of numbering/bulleting using any type of code?

Thanks!

B

p45cal
08-28-2007, 10:52 AM
Post the code you have so far.

chaneybran
08-28-2007, 10:57 AM
It is very sloppy but this is what I initially thought of:


Sub Insert_Task()

Dim i As Integer

Sheets("LookupLists").Select
Range("A25:B25").Copy
i = Sheets("LookupLists").Range("A25").Value
Sheets("Sheet1").Select
Range(Cells(6 + i, 1), Cells(6 + i, 1).Offset(0, 0)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End Sub


Where ListlookUP Sheet contains the MAX Value of the numbering column so it knows to continue from there.

lucas
08-28-2007, 11:11 AM
Please help as the title to your thread will cause serious help to avoid your thread....just a tip. Try to use a meaningful name for your threads and the right people will drop in.

I suggest you post your workbook and explain exactly what you're trying to do....

p45cal
08-28-2007, 02:31 PM
I agree with lucas, what you've provided doesn't really supply enough context (meat, if you will).
p45cal

malik641
08-28-2007, 03:09 PM
..doesn't really supply enough context (meat, if you will).
It's kind of like sloppy joe right now :rofl: j/k

Seriously though, an example workbook with what you have and what you need would should provide us with enough info.

Charlize
08-29-2007, 02:45 AM
Two routines as a possible way to do. One is the taskno and is place in column A (head title in A1 = Task). when you run the taskbutton, your cursor must be in column A. When you run the subtask button, you must have a tasknumber in the activecell (otherwise errors - and how do we know where we want the subtask to be added).Option Explicit
Sub new_task()
Dim cell As Range
Dim vno As Long
For Each cell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
If Val(cell.Value) > vno Then
vno = cell.Value
End If
Next cell
vno = vno + 1
Range("A" & Range("A" & Rows.Count).End(xlUp).Row + 1).Value = "'" & vno
Range("A" & Range("A" & Rows.Count).End(xlUp).Row).Resize(, 3).Interior.ColorIndex = 38
Range("A" & Range("A" & Rows.Count).End(xlUp).Row).Offset(0, 1) = _
Application.InputBox("Give your description ...", "Provide comments ...", Type:=2)
End Sub
Sub new_subtask()
Dim vno As Long
Dim subtaskno As Long
Dim lrow As Long
lrow = Range("A" & Rows.Count).End(xlUp).Row
vno = ActiveCell.Value
Do While ActiveCell.Offset(1, 0).Value = "*"
ActiveCell.Offset(1, 0).Select
If lrow < ActiveCell.Row Then Exit Do
subtaskno = subtaskno + 1
Loop
subtaskno = subtaskno + 1
lrow = lrow + 1
If lrow = ActiveCell.Row Then
ActiveCell.Resize(, 3).Interior.ColorIndex = xlColorIndexNone
ActiveCell.Value = "*"
ActiveCell.HorizontalAlignment = xlRight
ActiveCell.Offset(, 1).Value = "'" & vno & "." & subtaskno
ActiveCell.Offset(, 2).Value = _
Application.InputBox("Give your description ...", "Provide comments ...", Type:=2)
Else
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.Insert
ActiveCell.Resize(, 3).Interior.ColorIndex = xlColorIndexNone
ActiveCell.Value = "*"
ActiveCell.HorizontalAlignment = xlRight
ActiveCell.Offset(, 1).Value = "'" & vno & "." & subtaskno
ActiveCell.Offset(, 2).Value = _
Application.InputBox("Give your description ...", "Provide comments ...", Type:=2)
End If
End Sub