Announcement

Collapse
No announcement yet.

Automatic Task Priority re-numbering

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Automatic Task Priority re-numbering



    Hello,

    We have a list of tasks:
    • Many already done and flagged as “Complete”
    • Many flagged as “Pending”
    • About 10 tasks at a time ranked from do first = 1, to do last = 10.
      • When 1 is moved to “Complete”, 2 becomes 1, 3 becomes 2, etc.
      • Then one from the “Pending” group may be moved to 10 right away.... or not.
      • Then we sort the list.
      • This is done manually.
    At times we need to switch the order where, for example:
    • 9 gets higher priority and may become 2. Then 2 goes to 3, 3 to 4 etc.
    • Another time we may bounce a “Pending” task to a priority level already set. So, if task “Pending” moves to 2, then old 2 goes to 3, 3 to 4,...10 goes to 11.
    • Some times we move an assigned task, such as task 2 back to “Pending”, then 3 moves to 2, 4 to 3, etc.
    We are looking to increase the ranked number of tasks from the current 10 to about 60. Doing any task number renumbering manually will turn your hair gray!

    I would think that vba might be able to handle this via perhaps workbook selection change on the “Task Status” column (A) to see
    • what change was made and then renumber tasks when needed (when a number was added, changed or removed),
    • assuring we have consecutive numbering starting at 1
      • (the max number is not set and only determined by however many tasks are currently numbered - today we have sometimes only 6 and other times 12, and “tomorrow” we may have 50 one time and the next 65),
    • without duplicating task numbers,
    • and by preserving the order of tasks.
    Though sadly, i haven't got a clue nor was i able to find any bits here or online that might get me on the right track.

    Assistance with this project would be much appreciated.

    Thank you,

    Stefan





  • #2
    Hello,

    With such a highly sophisticated numbering method ...

    A sample file to illustrate your approach ... is a must ... !!!
    If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

    Comment


    • #3
      Hello Carim,

      Thank you for writing; point taken.

      Originally posted by Carim View Post
      A sample file to illustrate your approach ... is a must ... !!!
      I have mocked up a sample that I hope will help. - Not shown would be the step where, after the process renumbers the task #'s, the list would be sorted to put in proper order 1...5...10... etc.

      Hopefully the three samples will help you to help me.

      Thank you,

      Stefan
      Attached Files

      Comment


      • #4


        Hello,

        Let's take things step by step ... one case after the other ..

        See with case 1 is correctly handled with following

        Code:
        Private Sub Worksheet_Change(ByVal Target As Range)
        ' Deals with Case # 1
        If Target.Column <> 4 Then Exit Sub
        If IsEmpty(Target) Then Exit Sub
        Dim c As Range
        Dim rng As Range
        
            If Target = "Complete" Then
                Target.Offset(1, 0) = Target.Offset(-1, 0) + 1
                Set rng = Range(Cells(Target.Offset(3, 0), 4), Cells(10, 4))
                For Each c In rng
                  If IsNumeric(c) Then
                     c = c.Offset(-1, 0) + IIf(c.Offset(-1, 0) + 1 = Target, 2, 1)
                  End If
                Next c
            End If
        End Sub
        Hope this will help
        If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

        Comment

        Working...
        X