gpt-3.5-turbo-0613· OpenAI
WildChat-4.8M (AI2)
Can you please correct this VBA event, Several conditions are being triggered: Private…
- 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 IfServiceYes: 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 IfEnd Sub
- 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 = FalseEnd Sub