Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0301· OpenAIAi2 WildChat-4.8M (AI2)

Private Sub Worksheet_Change(ByVal Target As Range) 'CHECK AND MAKE SURE ONLY ONE CELL…

  1. User

    Private Sub Worksheet_Change(ByVal Target As Range) 'CHECK AND MAKE SURE ONLY ONE CELL IS SELECTED With Target If .CountLarge > 1 Then Exit Sub

    I have included comments in ALL CAPITALS to the uncommpleted code to explain my requirement. based on my COMMENTS, can you please complete the code.

    'CHECK IF CHANGE HAPPENED IN A2 If Not Intersect(Target, Range("$A2")) Is Nothing Then Application.EnableEvents = False ActiveSheet.Calculate 'recalculate sheet Application.EnableEvents = True

    'IF CHANGE HAPPENED IN A2 THEN SHOW RELEVANT MESSAGE DEPENDING ON THE VALUES IN G3 AND H3 Dim insured As Boolean Dim cleared As Boolean Dim contractor As String insured = (Range("G3").Value <> "") cleared = (Range("H3").Value <> "") contractor = Range("B3") If Not insured And Not cleared Then answer = MsgBox("Contractor " & contractor & " is not insured and has not been DBS’d. Create INSURANCE/DBS Request?", vbYesNo + vbQuestion, "Send details") ElseIf Not insured Then 'only G3 is empty answer = MsgBox("Contractor " & contractor & " is not insured. Create INSURANCE Request?", vbYesNo + vbQuestion, "Send details") ElseIf Not cleared Then 'only H3 is empty answer = MsgBox("Contractor " & contractor & " has not been DBS’d. Create DBS Request?", vbYesNo + vbQuestion, "Send details") Else 'both G3 and H3 have values

    'IF ANSWER IS YES MOVE TO THE WORK PERMIT SHEET AND EXIT THE SUB If answer = vbYes Then Sheets("Work Permit").Activate

    'IF ANSWER IS NO REMAIN IN CURRENT SHEET

    'IF CHANGE HAPPENED IN F2 THEN CHECK IF ALL 6 CELLS IN A2:F2 HAVE VALUES If Not Intersect(Target, Range("$F2")) Is Nothing Then If Application.CountA(Range("A2:F2")) = 6 Then

    'WHEN ALL CELLS HAVE VALUES CALL THE REQUEST SUB If Application.CountA(Range("A2:F2")) = 6 Then Call Request 'IF CHANGE HAPPENED IN F2 AND NOT ALL CELLS IN A2:F2 HAVE VALUES If Application.CountA(Range("A2:F2")) <> 6 Then

    'SHOW PROMPT "ALL FIELDS NEED TO BE FILLED IN" MsgBox ("All Fields need to be filled in")

    'AFTER THE PROMPT IS CLOSED, SELECT THE FIRST EMPTY CELL IN ROW 2 'WHEN ALL 6 CELLS HAVE BEEN COMPLETED THEN MOVE TO CALL REQUEST

  2. Assistant

    Range("A2:F2").SpecialCells(xlCellTypeBlanks)(1).Select End If End If End If End If End Sub

    Note: The code assumes that there is already a sub named "Request" that will be called when all fields in A2:F2 have been filled in.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.