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

Can you please correct this VBA event, Several conditions are being triggered: Private…

  1. User

    Can you please correct this VBA event, Several conditions are being triggered: Private Sub Worksheet_Change(ByVal Target As Range) Dim newRow As Long

    If Target.CountLarge > 1 Then Exit Sub
    
        If Not Intersect(Target, Range("I6:I505")) Is Nothing Then ' COLUMN I FOR SERVICE COMPLETED DATE
        
        If IsDate(Target.Value) And Target.Offset(0, -7).Value <> "Service" Then
        ActiveSheet.Range("B" & Target.Row).Select
        MsgBox "Please change the Issue description (column B) as required.", vbInformation, "ISSUE"
        End If
        Exit Sub
        
        If Not Intersect(Target, Range("I6:I505")) Is Nothing Then ' COLUMN I FOR SERVICE COMPLETED DATE
        
        If IsDate(Target.Value) And Target.Offset(0, -7).Value = "Service" Then
        If Target.Offset(0, -1).Value <= DateAdd("d", 14, Date) And Target.Value = "" Then
        Dim result As VbMsgBoxResult
        result = MsgBox("NEXT DUE DATE MORE THAN 30 DAYS FROM TODAY!" & vbCrLf & vbCrLf & _
                    "Have you entered the completed Date" & vbCrLf & vbCrLf & _
                    "using the format dd/mm/yyyy" & vbCrLf & vbCrLf & _
                    "Click YES to proceed - NO to Cancel", vbYesNo + vbQuestion)
        If result = vbNo Then
        Target.Value = ""
        Exit Sub
        End If
        If result = vbYes Then
        GoTo ServiceYes
        End If
    
        Else
        
        If IsDate(Target.Value) And Target.Offset(0, -7).Value = "Service" Then
        result = MsgBox("IS THIS SERVICE COMPLETED?" & vbCrLf & vbCrLf & _
                    "Have you entered the completed Date" & vbCrLf & vbCrLf & _
                    "using the format dd/mm/yyyy" & vbCrLf & vbCrLf & _
                    "Click YES to proceed - NO to Cancel", vbYesNo + vbQuestion)
        If result = vbNo Then
        Target.Value = ""
        Exit Sub
        End If
        If result = vbYes Then
        GoTo ServiceYes
        End If
    

    ServiceYes: ActiveSheet.Range("G" & Target.Row).Select MsgBox "Thank you for entering Completed Service Date" & vbCrLf & vbCrLf & _ "Please proceed to enter Service Schedule as number of days", vbInformation, "DATE" End If End If

    If Not Intersect(Target, Range("G6:G505")) Is Nothing Then ' COLUMN G FOR SERVICE SCHEDULE DAYS
    Application.EnableEvents = True
        If Target.Offset(0, -5).Value <> "Service" And Target.Offset(0, -5).Value <> "Serviced" Then
            Exit Sub
        End If
        
        If Not doubleClickFlag And Target.Value <> "" And Not IsDate(Target.Offset(0, 2).Value) Then
            MsgBox "Date Completed not present", vbCritical, "DATE"
            Application.EnableEvents = False
            Target.Value = ""
            Application.EnableEvents = True
        ElseIf Target.Value <> "" And Target.Offset(0, -5).Value = "Service" And IsDate(Target.Offset(0, 2).Value) Then
            MsgBox "A New Service row will now be created", vbInformation, "NEW SERVICE"
            Target.Offset(0, -5).Value = "Serviced"
            Target.Offset(0, 1).Value = ""
            
    newRow = Cells(Rows.Count, "B").End(xlUp).Row + 1
            With ActiveSheet
                .Range("B" & newRow).Value = "Service"
                .Range("C" & newRow).Value = Target.Offset(0, 2).Value
                .Range("F" & newRow).Value = Target.Offset(0, -1).Value
                .Range("J" & newRow).Value = Target.Offset(0, 3).Value
                .Range("L" & newRow).Value = Target.Offset(0, 5).Value
                .Range("H" & newRow).Value = Target.Offset(0, 2).Value + Target.Value
            End With
    End If
    End If
    End If
    End If
    
    If Target.Column = 5 Then
        ColumnList Target
    End If
    
    doubleClickFlag = False
    
    End If
    

    End Sub

  2. Assistant

    Private Sub Worksheet_Change(ByVal Target As Range) Dim newRow As Long

    If Target.CountLarge > 1 Then Exit Sub
    
    If Not Intersect(Target, Range("I6:I505")) Is Nothing Then ' COLUMN I FOR SERVICE COMPLETED DATE
        
        If IsDate(Target.Value) And Target.Offset(0, -7).Value <> "Service" Then
            ActiveSheet.Range("B" & Target.Row).Select
            MsgBox "Please change the Issue description (column B) as required.", vbInformation, "ISSUE"
        End If
        Exit Sub
    End If
    
    If Not Intersect(Target, Range("I6:I505")) Is Nothing Then ' COLUMN I FOR SERVICE COMPLETED DATE
        
        If IsDate(Target.Value) And Target.Offset(0, -7).Value = "Service" Then
            If Target.Offset(0, -1).Value <= DateAdd("d", 14, Date) And Target.Value = "" Then
                Dim result As VbMsgBoxResult
                result = MsgBox("NEXT DUE DATE MORE THAN 30 DAYS FROM TODAY!" & vbCrLf & vbCrLf & _
                                "Have you entered the completed Date" & vbCrLf & vbCrLf & _
                                "using the format dd/mm/yyyy" & vbCrLf & vbCrLf & _
                                "Click YES to proceed - NO to Cancel", vbYesNo + vbQuestion)
                If result = vbNo Then
                    Target.Value = ""
                    Exit Sub
                ElseIf result = vbYes Then
                    GoTo ServiceYes
                End If
            Else
                If IsDate(Target.Value) And Target.Offset(0, -7).Value = "Service" Then
                    result = MsgBox("IS THIS SERVICE COMPLETED?" & vbCrLf & vbCrLf & _
                                    "Have you entered the completed Date" & vbCrLf & vbCrLf & _
                                    "using the format dd/mm/yyyy" & vbCrLf & vbCrLf & _
                                    "Click YES to proceed - NO to Cancel", vbYesNo + vbQuestion)
                    If result = vbNo Then
                        Target.Value = ""
                        Exit Sub
                    ElseIf result = vbYes Then
                        GoTo ServiceYes
                    End If
                End If
            End If
        End If
    End If
    
    If Not Intersect(Target, Range("G6:G505")) Is Nothing Then ' COLUMN G FOR SERVICE SCHEDULE DAYS
        Application.EnableEvents = True
        If Target.Offset(0, -5).Value <> "Service" And Target.Offset(0, -5).Value <> "Serviced" Then
            Exit Sub
        End If
        
        If Not doubleClickFlag And Target.Value <> "" And Not IsDate(Target.Offset(0, 2).Value) Then
            MsgBox "Date Completed not present", vbCritical, "DATE"
            Application.EnableEvents = False
            Target.Value = ""
            Application.EnableEvents = True
        ElseIf Target.Value <> "" And Target.Offset(0, -5).Value = "Service" And IsDate(Target.Offset(0, 2).Value) Then
            MsgBox "A New Service row will now be created", vbInformation, "NEW SERVICE"
            Target.Offset(0, -5).Value = "Serviced"
            Target.Offset(0, 1).Value = ""
            
            newRow = Cells(Rows.Count, "B").End(xlUp).Row + 1
            With ActiveSheet
                .Range("B" & newRow).Value = "Service"
                .Range("C" & newRow).Value = Target.Offset(0, 2).Value
                .Range("F" & newRow).Value = Target.Offset(0, -1).Value
                .Range("J" & newRow).Value = Target.Offset(0, 3).Value
                .Range("L" & newRow).Value = Target.Offset(0, 5).Value
                .Range("H" & newRow).Value = Target.Offset(0, 2).Value + Target.Value
            End With
        End If
    End If
    
    If Target.Column = 5 Then
        ColumnList Target
    End If
    
    doubleClickFlag = False
    

    End Sub