' VBA Code to Initialize Workbook and Generate Task Tracker
' Place this code in a standard module (Insert > Module) in the VBA editor.

Option Explicit

' Subroutine to initialize the workbook: create sheets, set up columns, add button
Sub InitializeTaskTracker()
    Dim wsTaskTracker As Worksheet
    Dim wsSettings As Worksheet
    Dim shp As Shape
    Dim btnLeft As Double, btnTop As Double, btnWidth As Double, btnHeight As Double
    Dim btnName As String
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    ' Attempt to set Task Tracker sheet; if it doesn't exist, create it
    On Error Resume Next
    Set wsTaskTracker = ThisWorkbook.Worksheets("Task Tracker")
    On Error GoTo 0
    If wsTaskTracker Is Nothing Then
        Set wsTaskTracker = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        wsTaskTracker.Name = "Task Tracker"
    Else
        ' Clear existing content
        wsTaskTracker.Cells.Clear
    End If
    
    ' Attempt to set Settings sheet; if it doesn't exist, create it
    On Error Resume Next
    Set wsSettings = ThisWorkbook.Worksheets("Settings")
    On Error GoTo 0
    If wsSettings Is Nothing Then
        Set wsSettings = ThisWorkbook.Worksheets.Add(After:=wsTaskTracker)
        wsSettings.Name = "Settings"
    Else
        ' Clear existing content
        wsSettings.Cells.Clear
    End If
    
    ' Set up columns in Task Tracker (Sheet1)
    With wsTaskTracker
        .Range("A1").Value = "Date Offered"
        .Range("B1").Value = "Date Completed"
        .Range("C1").Value = "Time Started"
        .Range("D1").Value = "Time Finished"
        .Range("E1").Value = "Duration (mins)"
        .Range("F1").Value = "Reference Number"
        .Range("G1").Value = "Function/Job Classification"
        .Range("H1").Value = "Remarks/Notes"
        
        ' Format header as bold and apply background color
        With .Range("A1:H1")
            .Font.Bold = True
            .Interior.Color = RGB(200, 200, 200)
        End With
        
        ' Adjust column widths
        .Columns("A:H").AutoFit
    End With
    
    ' Set up Settings sheet
    With wsSettings
        ' Define parameters and labels
        .Range("A1").Value = "Parameter"
        .Range("B1").Value = "Value"
        
        .Range("A2").Value = "Start Time (m/d/yyyy h:mm AM/PM)"
        ' Example: 10/5/2023 8:00 AM (User should modify as needed)
        .Range("B2").Value = "10/5/2023 8:00 AM"
        
        .Range("A3").Value = "First Break Start Time (m/d/yyyy h:mm AM/PM)"
        .Range("B3").Value = "10/5/2023 10:00 AM"
        
        .Range("A4").Value = "First Break Duration (mins)"
        .Range("B4").Value = 13
        
        .Range("A5").Value = "Lunch Break Start Time (m/d/yyyy h:mm AM/PM)"
        .Range("B5").Value = "10/5/2023 12:30 PM"
        
        .Range("A6").Value = "Lunch Break Duration (mins)"
        .Range("B6").Value = 55
        
        .Range("A7").Value = "Last Break Start Time (m/d/yyyy h:mm AM/PM)"
        .Range("B7").Value = "10/5/2023 3:30 PM"
        
        .Range("A8").Value = "Last Break Duration (mins)"
        .Range("B8").Value = 15
        
        ' Headers for Org Codes
        .Range("C9").Value = "Org Codes"
        .Range("C9").Font.Bold = True
        .Range("C9").Interior.Color = RGB(200, 200, 200)
        
        ' Example Org Codes
        .Range("C10").Value = "ORG001"
        .Range("C11").Value = "ORG002"
        .Range("C12").Value = "ORG003"
        ' Add more Org Codes as needed
        
        ' Format headers for Parameter
        With .Range("A1:B1")
            .Font.Bold = True
            .Interior.Color = RGB(200, 200, 200)
        End With
        
        ' Adjust column widths
        .Columns("A:C").AutoFit
    End With
    
    ' Remove existing "Generate Tasks" button if it exists
    btnName = "btnGenerateTasks"
    On Error Resume Next
    wsTaskTracker.Shapes(btnName).Delete
    On Error GoTo 0
    
    ' Add a button to Task Tracker sheet to run GenerateTasks macro using Shapes.AddFormControl
    With wsTaskTracker
        ' Define button dimensions and position
        btnLeft = 10
        btnTop = 10
        btnWidth = 120
        btnHeight = 30
        
        ' Add the button as a Form Control
        Set shp = .Shapes.AddFormControl(Type:=xlButtonControl, Left:=btnLeft, Top:=btnTop, Width:=btnWidth, Height:=btnHeight)
        With shp
            .OnAction = "GenerateTasks"
            .Name = btnName
            .TextFrame.Characters.Text = "Generate Tasks"
            .TextFrame.HorizontalAlignment = xlHAlignCenter
            .TextFrame.VerticalAlignment = xlVAlignCenter
            .Fill.ForeColor.RGB = RGB(0, 112, 192) ' Optional: Button color
            .Fill.Transparency = 0
            .Line.Visible = msoFalse ' Optional: Remove button border
            .TextFrame.Characters.Font.Color = RGB(255, 255, 255) ' Optional: Text color
            .TextFrame.Characters.Font.Bold = True
        End With
    End With
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
    MsgBox "Workbook initialized successfully! Please configure the 'Settings' sheet before generating tasks.", vbInformation
End Sub

' Subroutine to generate tasks based on Settings
Sub GenerateTasks()
    Dim wsTracker As Worksheet
    Dim wsSettings As Worksheet
    
    Set wsTracker = ThisWorkbook.Worksheets("Task Tracker")
    Set wsSettings = ThisWorkbook.Worksheets("Settings")
    
    ' Read Start Time
    Dim startTime As Date
    If IsDate(wsSettings.Range("B2").Value) Then
        startTime = wsSettings.Range("B2").Value
    Else
        MsgBox "Please enter a valid Start Time in the 'Settings' sheet (cell B2).", vbExclamation
        Exit Sub
    End If
    
    ' Read Break Start Times and Durations
    Dim break1Start As Date, break1Duration As Double
    Dim lunchBreakStart As Date, lunchBreakDuration As Double
    Dim break2Start As Date, break2Duration As Double
    
    If IsDate(wsSettings.Range("B3").Value) Then
        break1Start = wsSettings.Range("B3").Value
    Else
        MsgBox "Please enter a valid First Break Start Time in the 'Settings' sheet (cell B3).", vbExclamation
        Exit Sub
    End If
    
    If IsNumeric(wsSettings.Range("B4").Value) Then
        break1Duration = wsSettings.Range("B4").Value
    Else
        MsgBox "Please enter a valid First Break Duration in minutes in the 'Settings' sheet (cell B4).", vbExclamation
        Exit Sub
    End If
    
    If IsDate(wsSettings.Range("B5").Value) Then
        lunchBreakStart = wsSettings.Range("B5").Value
    Else
        MsgBox "Please enter a valid Lunch Break Start Time in the 'Settings' sheet (cell B5).", vbExclamation
        Exit Sub
    End If
    
    If IsNumeric(wsSettings.Range("B6").Value) Then
        lunchBreakDuration = wsSettings.Range("B6").Value
    Else
        MsgBox "Please enter a valid Lunch Break Duration in minutes in the 'Settings' sheet (cell B6).", vbExclamation
        Exit Sub
    End If
    
    If IsDate(wsSettings.Range("B7").Value) Then
        break2Start = wsSettings.Range("B7").Value
    Else
        MsgBox "Please enter a valid Last Break Start Time in the 'Settings' sheet (cell B7).", vbExclamation
        Exit Sub
    End If
    
    If IsNumeric(wsSettings.Range("B8").Value) Then
        break2Duration = wsSettings.Range("B8").Value
    Else
        MsgBox "Please enter a valid Last Break Duration in minutes in the 'Settings' sheet (cell B8).", vbExclamation
        Exit Sub
    End If
    
    ' Read Org Codes from Settings, Column C, starting from C10 down
    Dim orgCodes As Collection
    Set orgCodes = GetOrgCodes(wsSettings, 3, 10) ' Column C, starting from row 10
    
    If orgCodes.Count = 0 Then
        MsgBox "Please enter at least one Org Code in the 'Settings' sheet (column C starting from row 10).", vbExclamation
        Exit Sub
    End If
    
    ' Define tasks with their probabilities, duration ranges, and remarks
    Dim taskNames As Variant
    Dim taskWeights As Variant
    Dim taskMinDurations As Variant
    Dim taskMaxDurations As Variant
    Dim taskRemarks As Variant
    Dim numTasks As Integer
    
    taskNames = Array("Checking Organization Records", "Creating Organization Records", "Email Management", _
                     "PowerBi", "Updating Organization", "Uploading Edocs", "Verifying Temporary Accounts", "OBPI Adhoc")
                     
    taskWeights = Array(80, 10, 80, 40, 30, 60, 80, 10) ' Corresponding to percentages
    taskMinDurations = Array(1, 10, 2, 2, 5, 1, 1, 10)
    taskMaxDurations = Array(3, 12, 3, 3, 6, 2, 2, 20)
    taskRemarks = Array("Remark for Checking Organization Records", _
                        "Remark for Creating Organization Records", _
                        "Remark for Email Management", _
                        "Remark for PowerBi", _
                        "Remark for Updating Organization", _
                        "Remark for Uploading Edocs", _
                        "Remark for Verifying Temporary Accounts", _
                        "Remark for OBPI Adhoc")
    
    numTasks = UBound(taskNames) - LBound(taskNames) + 1
    
    ' Define total desired duration in minutes (7 hrs 30 mins to 7 hrs 35 mins)
    Dim minTotalDuration As Double, maxTotalDuration As Double
    minTotalDuration = 450 ' 7*60 + 30
    maxTotalDuration = 455 ' 7*60 + 35
    
    ' Initialize variables for task generation
    Dim currentTime As Date
    currentTime = startTime
    Dim totalDuration As Double
    totalDuration = 0
    
    ' Define Breaks as arrays
    Dim breakStarts(1 To 3) As Date
    Dim breakDurations(1 To 3) As Double
    
    breakStarts(1) = break1Start
    breakDurations(1) = break1Duration
    
    breakStarts(2) = lunchBreakStart
    breakDurations(2) = lunchBreakDuration
    
    breakStarts(3) = break2Start
    breakDurations(3) = break2Duration
    
    ' Sort breaks by start time in ascending order
    Dim i As Integer, j As Integer
    Dim tempStart As Date, tempDuration As Double
    For i = 1 To 2
        For j = i + 1 To 3
            If breakStarts(i) > breakStarts(j) Then
                tempStart = breakStarts(i)
                tempDuration = breakDurations(i)
                breakStarts(i) = breakStarts(j)
                breakDurations(i) = breakDurations(j)
                breakStarts(j) = tempStart
                breakDurations(j) = tempDuration
            End If
        Next j
    Next i
    
    ' Initialize next break index
    Dim nextBreak As Integer
    nextBreak = 1
    
    ' Clear previous data in Task Tracker sheet (from row 2 down)
    wsTracker.Range("A2:H" & wsTracker.Rows.Count).ClearContents
    
    ' Start generating tasks
    Dim taskRow As Long
    taskRow = 2 ' Start from row 2
    
    Do While totalDuration < minTotalDuration And orgCodes.Count > 0
        ' Select a random Org Code from the collection
        Dim selectedOrg As String
        selectedOrg = orgCodes.Item(Int((orgCodes.Count) * Rnd + 1))
        
        ' Remove the selected Org Code from the collection to prevent reuse
        orgCodes.RemoveItem selectedOrg
        
        ' Select a Function based on weighted probabilities
        Dim selectedTaskIndex As Integer
        selectedTaskIndex = GetWeightedRandomTaskIndex(taskWeights, numTasks)
        
        Dim selectedTask As String
        selectedTask = taskNames(selectedTaskIndex)
        
        ' Get corresponding duration range
        Dim minDuration As Integer, maxDuration As Integer
        minDuration = taskMinDurations(selectedTaskIndex)
        maxDuration = taskMaxDurations(selectedTaskIndex)
        
        ' Assign a random duration within the range
        Dim taskDuration As Integer
        Randomize
        taskDuration = Int((maxDuration - minDuration + 1) * Rnd + minDuration)
        
        ' Check if adding this duration would exceed maxTotalDuration
        If (totalDuration + taskDuration) > maxTotalDuration Then
            taskDuration = maxTotalDuration - totalDuration
            If taskDuration <= 0 Then Exit Do
        End If
        
        ' Check if task overlaps with a break
        Dim potentialEndTime As Date
        potentialEndTime = currentTime + (taskDuration / (24 * 60))
        
        Dim overlap As Boolean
        overlap = False
        For i = nextBreak To 3
            If (currentTime < breakStarts(i) + (breakDurations(i) / (24 * 60))) And (potentialEndTime > breakStarts(i)) Then
                overlap = True
                Exit For
            End If
        Next i
        
        If overlap Then
            ' Advance current time to after the overlapping break
            For i = nextBreak To 3
                If (currentTime < breakStarts(i) + (breakDurations(i) / (24 * 60))) And (potentialEndTime > breakStarts(i)) Then
                    currentTime = breakStarts(i) + (breakDurations(i) / (24 * 60))
                    nextBreak = i + 1
                    Exit For
                End If
            Next i
            GoTo ContinueLoop
        End If
        
        ' Get the corresponding Remark for the selected Function
        Dim funcRemark As String
        funcRemark = taskRemarks(selectedTaskIndex)
        
        ' Write to Task Tracker sheet
        With wsTracker
            .Range("A" & taskRow).Value = startTime
            .Range("B" & taskRow).Value = potentialEndTime
            .Range("C" & taskRow).Value = currentTime
            .Range("D" & taskRow).Value = potentialEndTime
            .Range("E" & taskRow).Value = taskDuration
            .Range("F" & taskRow).Value = selectedOrg
            .Range("G" & taskRow).Value = selectedTask
            .Range("H" & taskRow).Value = funcRemark
        End With
        
        ' Update variables
        currentTime = potentialEndTime
        totalDuration = totalDuration + taskDuration
        taskRow = taskRow + 1
        
ContinueLoop:
    Loop
    
    ' Optionally, format the Task Tracker sheet
    With wsTracker
        .Columns("A:H").AutoFit
        ' Format columns A, B as Date/Time and C, D as Time
        .Columns("A").NumberFormat = "m/d/yyyy h:mm AM/PM"
        .Columns("B").NumberFormat = "m/d/yyyy h:mm AM/PM"
        .Columns("C").NumberFormat = "h:mm AM/PM"
        .Columns("D").NumberFormat = "h:mm AM/PM"
    End With
    
    MsgBox "Tasks generated successfully in the 'Task Tracker' sheet.", vbInformation
End Sub

' Function to get Org Codes from a column starting from a specific row
Function GetOrgCodes(ws As Worksheet, col As Integer, startRow As Integer) As Collection
    Dim orgCodes As New Collection
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, col).End(xlUp).Row
    Dim i As Long
    For i = startRow To lastRow
        If Trim(ws.Cells(i, col).Value) <> "" Then
            orgCodes.Add ws.Cells(i, col).Value
        End If
    Next i
    Set GetOrgCodes = orgCodes
End Function

' Function to perform weighted random selection
' probabilities array contains weights (percentages)
' numTasks is the number of tasks
Function GetWeightedRandomTaskIndex(probabilities As Variant, numTasks As Integer) As Integer
    Dim totalWeight As Double
    Dim i As Integer
    totalWeight = 0
    For i = LBound(probabilities) To UBound(probabilities)
        totalWeight = totalWeight + probabilities(i)
    Next i
    
    Dim randVal As Double
    randVal = Rnd * totalWeight
    
    Dim cumulativeWeight As Double
    cumulativeWeight = 0
    For i = LBound(probabilities) To UBound(probabilities)
        cumulativeWeight = cumulativeWeight + probabilities(i)
        If randVal <= cumulativeWeight Then
            GetWeightedRandomTaskIndex = i
            Exit Function
        End If
    Next i
    
    ' In case of rounding errors
    GetWeightedRandomTaskIndex = UBound(probabilities)
End Function

analyze this code debug it fix possible errors and make it run smoothly without error