VBA to auto-generate a task ID (Unique and sequenced)

[titoexcel]

Hi,

I am trying to kind of replicate what MS Project does but in excel since my company is fairly cheap and won't pay $600 for the license (or whatever the amount).

So i am trying to replicate the process of MS Project on task IDs (unique and if you insert a row somewhere that it generates a unique ID). Reason I need this because of "predecessors".

So if i have task ID # 1 (start time: 9/15/2015 9:00 AM and finish time 09/16/2015 8:00 AM), and task ID #2 is dependent on Task ID #1, i want the excel to automatically shift times on task #2 if time on Task ID #1 changes.

I wrote some VBA but its a function rather

Public Function GetGUID() As String    GetGUID = Mid$(CreateObject("Scriptlet.TypeLib").GUID, 2, 2)
End Function

I really don't want a function but not sure how to even start. Any help is much appreciated.

 

[RickXL]

Hi,

A few things:

I don't know how much of the Project functionality you are hoping to replicate in Excel but the cost of Project is an indication of how hard it will be to recreate.
I don't think that just using two characters from the GUID will give you a unique ID. You need to use the full 36 characters to guarantee uniqueness.

You may well be able to replicate the Project ID functionality by creating a new ID based on the maximum one used so far in that project. This would entail saving the current maximum value somewhere - probably in another (hidden?) worksheet.

The following macro needs to be pasted into a Worksheet Code Module for the sheet you want to use.

It assumes many things, including:
The unique ID will be in column A;
The description will be in column B and it will always be filled in;
There will be a worksheet called "Hidden" available;
Cell A1 on the Hidden sheet will store the current maximum value.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MaxID As Long, c As Range
    On Error GoTo Error
    Application.EnableEvents = False
    If Not Intersect(Target, Columns("B")) Is Nothing Then
        For Each c In Intersect(Target, Columns("B"))
            If Cells(c.Row, "A").Value = vbNullString Then
                With ThisWorkbook.Worksheets("Hidden")
                    MaxID = .Range("A1").Value + 1
                    .Range("A1").Value = MaxID
                End With
                Cells(c.Row, "A").Value = MaxID
            End If
        Next
    End If
Error:
    Application.EnableEvents = True
End Sub

What is does:
It reacts to every change to the worksheet but only does something if column B is changed;
However many rows are changed, it will supply an ID to all the blank cells in column A;
The current maximum value will be stored back in the Hidden sheet.

Note: If you overtype an ID - all bets are off!

 

[titoexcel]

Thanks for your help and apologies for a late response. I am testing this right now. Thank you for the help.