Case Management System

' Case Management System
' Sheet structure: CaseID (A), Status (B), AllocatedTo (C), Picked (D), Comment (E),
' ActionTaken (F), PickedTime (G), ActionTime (H), DroppedTime (I)

' Global variable to store active case per user
Public activeCaseID As String
Public activeUser As String

Sub GetWork()
    ' Assign next available case to team member
    On Error GoTo ErrorHandler

    ' Prompt for team member name
    Dim userName As String
    userName = InputBox("Enter your name:", "Get Work")

    If userName = "" Then Exit Sub

    ' Check if user has an active case
    If activeUser = userName And activeCaseID <> "" Then
        MsgBox "You must submit your current case (ID: " & activeCaseID & ") before getting a new one.", vbExclamation
        Exit Sub
    End If

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Dump")

    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Find first unassigned case for this user
    Dim i As Long
    Dim foundCase As Boolean
    foundCase = False

    For i = 2 To lastRow
        If ws.Cells(i, 3).Value = userName And ws.Cells(i, 4).Value = "" Then
            ' Found available case
            Dim caseID As String
            Dim caseStatus As String

            caseID = ws.Cells(i, 1).Value
            caseStatus = ws.Cells(i, 2).Value

            ' Mark as picked
            ws.Cells(i, 4).Value = "Y"
            ws.Cells(i, 7).Value = Now ' PickedTime

            ' Store active case
            activeCaseID = caseID
            activeUser = userName

            ' Show case details
            MsgBox "Case assigned!" & vbCrLf & vbCrLf & _
                   "Case ID: " & caseID & vbCrLf & _
                   "Status: " & caseStatus, vbInformation

            foundCase = True
            Exit For
        End If
    Next i

' ... 147 more lines hidden ...
'
' Sign up for free to view the complete code
' Visit: vbacode.io