UserFormModule.bas

Attribute VB_Name = "UserFormModule"
' Manufacturing Workflow Scheduler - User Form Module
' Created: April 2025
'
' This module contains code for handling user form interactions

Option Explicit

' Show the workflow visualization form
Public Sub ShowWorkflowVisualization()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    
    ' Reference to workflow sheet
    Set ws = ThisWorkbook.Sheets("WorkflowSetup")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' Create form
    frmWorkflowViz.Show
End Sub

' Setup the workflow visualization form
Public Sub SetupWorkflowVisualizationForm(frm As Object)
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim steps As Collection
    Dim connections As Collection
    
    ' Reference to workflow sheet
    Set ws = ThisWorkbook.Sheets("WorkflowSetup")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    Set steps = New Collection
    Set connections = New Collection
    
    ' Get steps and connections
    For i = 2 To lastRow
        Dim stepID As String
        Dim stepName As String
        Dim prerequisites As String
        
        stepID = ws.Cells(i, 1).Value
        stepName = ws.Cells(i, 2).Value
        prerequisites = ws.Cells(i, 5).Value
        
        ' Add step
        Dim step As Object
        Set step = CreateObject("Scripting.Dictionary")
        step.Add "ID", stepID
        step.Add "Name", stepName
        steps.Add step
        
        ' Add connections
        If Trim(prerequisites) <> "" Then
            Dim prereqs As Variant
            prereqs = Split(prerequisites, ",")
            
            Dim j As Long
            For j = 0 To UBound(prereqs)
                Dim conn As Object
                Set conn = CreateObject("Scripting.Dictionary")
                conn.Add "From", Trim(prereqs(j))
                conn.Add "To", stepID
                connections.Add conn
            Next j
        End If
    Next i
    
    ' Draw workflow on form
    DrawWorkflow frm, steps, connections
End Sub

' Draw workflow on form
Private Sub DrawWorkflow(frm As Object, steps As Collection, connections As Collection)
    ' This would be the implementation for drawing the workflow diagram
    ' For now, we'll leave this as a placeholder
    ' In a real implementation, this would use VBA graphics objects to create
    ' a visual representation of the workflow with boxes and arrows
    
    ' Draw steps
    Dim i As Long
    Dim xPos As Long, yPos As Long
    Dim boxWidth As Long, boxHeight As Long
    
    boxWidth = 100
    boxHeight = 50
    xPos = 50
    yPos = 50
    
    For i = 1 To steps.Count
        ' Draw box
        frm.Controls.Add "Forms.Label.1", "Step" & i, True
        With frm.Controls("Step" & i)
            .Left = xPos
            .Top = yPos
            .Width = boxWidth
            .Height = boxHeight
            .Caption = steps(i)("ID") & ": " & steps(i)("Name")
            .BorderStyle = 1 ' fmBorderStyleSingle
        End With
        
        ' Update position for next box
        yPos = yPos + boxHeight + 30
        
        ' Reset y position and move x position if we're at the bottom of the form
        If yPos > frm.InsideHeight - boxHeight Then
            yPos = 50
            xPos = xPos + boxWidth + 50
        End If
    Next i
    
    ' In a real implementation, we would also draw lines for connections
End Sub

' Update resource utilization chart
Public Sub UpdateResourceChart()
    Dim wsSchedule As Worksheet
    Dim wsChart As Worksheet
    Dim lastRow As Long
    Dim resources As Collection
    Dim i As Long
    
    ' Get references to sheets
    Set wsSchedule = ThisWorkbook.Sheets("Schedule")
    
    ' Create chart sheet if it doesn't exist
    If Not SheetExists("ResourceChart") Then
        ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)).Name = "ResourceChart"
    End If
    Set wsChart = ThisWorkbook.Sheets("ResourceChart")
    
    ' Clear chart sheet
    wsChart.UsedRange.Clear
    
    ' Get data from schedule
    lastRow = wsSchedule.Cells(wsSchedule.Rows.Count, 1).End(xlUp).Row
    
    ' Get unique resources
    Set resources = New Collection
    On Error Resume Next
    For i = 2 To lastRow
        resources.Add wsSchedule.Cells(i, 6).Value, CStr(wsSchedule.Cells(i, 6).Value)
    Next i
    On Error GoTo 0
    
    ' Create resource utilization data
    wsChart.Range("A1").Value = "Resource"
    wsChart.Range("B1").Value = "Total Hours"
    
    Dim row As Long
    row = 2
    
    Dim resource As Variant
    For Each resource In resources
        wsChart.Cells(row, 1).Value = resource
        
        ' Calculate total hours for resource
        Dim totalHours As Double
        totalHours = 0
        
        For i = 2 To lastRow
            If wsSchedule.Cells(i, 6).Value = resource Then
                totalHours = totalHours + wsSchedule.Cells(i, 9).Value
            End If
        Next i
        
        wsChart.Cells(row, 2).Value = totalHours
        row = row + 1
    Next resource
    
    ' Format
    wsChart.Range("A1:B1").Font.Bold = True
    wsChart.UsedRange.Columns.AutoFit
    
    ' Create chart
    If wsChart.ChartObjects.Count > 0 Then
        wsChart.ChartObjects(1).Delete
    End If
    
    Dim chartObj As ChartObject
    Set chartObj = wsChart.ChartObjects.Add(100, 100, 400, 300)
    
    With chartObj.Chart
        .SetSourceData Source:=wsChart.Range("A1:B" & row - 1)
        .ChartType = xlColumnClustered
        .HasTitle = True
        .ChartTitle.Text = "Resource Utilization (Hours)"
        .Axes(xlValue).HasTitle = True
        .Axes(xlValue).AxisTitle.Text = "Hours"
        .Axes(xlCategory).HasTitle = True
        .Axes(xlCategory).AxisTitle.Text = "Resource"
    End With
End Sub

' Check if a sheet exists
Private Function SheetExists(sheetName As String) As Boolean
    Dim ws As Worksheet
    
    On Error Resume Next
    Set ws = ThisWorkbook.Sheets(sheetName)
    On Error GoTo 0
    
    SheetExists = Not ws Is Nothing
End Function

' Generate a schedule summary report
Public Sub GenerateScheduleSummary()
    Dim wsSchedule As Worksheet
    Dim wsReport As Worksheet
    Dim lastRow As Long
    Dim workOrders As Collection
    Dim i As Long
    
    ' Get references to sheets
    Set wsSchedule = ThisWorkbook.Sheets("Schedule")
    Set wsReport = ThisWorkbook.Sheets("Reports")
    
    ' Clear report sheet
    wsReport.UsedRange.Clear
    
    ' Get data from schedule
    lastRow = wsSchedule.Cells(wsSchedule.Rows.Count, 1).End(xlUp).Row
    
    ' Get unique work orders
    Set workOrders = New Collection
    On Error Resume Next
    For i = 2 To lastRow
        workOrders.Add wsSchedule.Cells(i, 1).Value, CStr(wsSchedule.Cells(i, 1).Value)
    Next i
    On Error GoTo 0
    
    ' Create summary headers
    wsReport.Range("A1").Value = "Work Order"
    wsReport.Range("B1").Value = "Product"
    wsReport.Range("C1").Value = "Earliest Start"
    wsReport.Range("D1").Value = "Latest Finish"
    wsReport.Range("E1").Value = "Total Duration"
    wsReport.Range("F1").Value = "Step Count"
    
    Dim row As Long
    row = 2
    
    Dim workOrder As Variant
    For Each workOrder In workOrders
        Dim woProduct As String
        Dim earliestStart As Date
        Dim latestFinish As Date
        Dim totalDuration As Double
        Dim stepCount As Long
        
        ' Initialize values
        earliestStart = DateValue("2099-12-31")
        latestFinish = DateValue("1900-01-01")
        totalDuration = 0
        stepCount = 0
        
        ' Find data for this work order
        For i = 2 To lastRow
            If wsSchedule.Cells(i, 1).Value = workOrder Then
                ' Get product
                woProduct = wsSchedule.Cells(i, 2).Value
                
                ' Check earliest start
                If wsSchedule.Cells(i, 7).Value < earliestStart Then
                    earliestStart = wsSchedule.Cells(i, 7).Value
                End If
                
                ' Check latest finish
                If wsSchedule.Cells(i, 8).Value > latestFinish Then
                    latestFinish = wsSchedule.Cells(i, 8).Value
                End If
                
                ' Add duration
                totalDuration = totalDuration + wsSchedule.Cells(i, 9).Value
                
                ' Count step
                stepCount = stepCount + 1
            End If
        Next i
        
        ' Write summary row
        wsReport.Cells(row, 1).Value = workOrder
        wsReport.Cells(row, 2).Value = woProduct
        wsReport.Cells(row, 3).Value = earliestStart
        wsReport.Cells(row, 4).Value = latestFinish
        wsReport.Cells(row, 5).Value = totalDuration
        wsReport.Cells(row, 6).Value = stepCount
        
        row = row + 1
    Next workOrder
    
    ' Format
    wsReport.Range("A1:F1").Font.Bold = True
    wsReport.Range("C:D").NumberFormat = "yyyy-mm-dd"
    wsReport.Range("E:E").NumberFormat = "0.00"
    wsReport.UsedRange.Columns.AutoFit
    
    ' Create table
    If Not wsReport.ListObjects.Count > 0 Then
        wsReport.ListObjects.Add(xlSrcRange, wsReport.UsedRange, , xlYes).Name = "SummaryTable"
    Else
        wsReport.ListObjects(1).Resize wsReport.UsedRange
    End If
End Sub