Private Sub Worksheet_Change(ByVal Target As Range) Dim rngI As Range Dim rngG As Range…
- User
Private Sub Worksheet_Change(ByVal Target As Range) Dim rngI As Range Dim rngG As Range Dim cell As Range Dim innercell As Range
If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Range(“I6:I506”)) Is Nothing Then If IsDate(Target.Value) Then If Target.Offset(0, -7).Value = “Service” Then MsgBox “Thank you for entering Service Date Completed. Please proceed to enter Service Schedule as number of days.” Range(“G” & Target.Row).Select Exit Sub Else MsgBox “Please change the Issue description ( column B ) as required.” Range(“B” & Target.Row).Select Exit Sub End If End If End If
If Not Intersect(Target, Range(“G6:G506”)) Is Nothing Then For Each cell In Intersect(Target, Range(“G6:G506”)) If cell.Value <> “” Then ’ Check if the value in the cell two columns to the right of the current cell is empty Dim rightCell As Range Set rightCell = cell.Offset(0, 2) If rightCell.Value = “” Then ’ Display a message box MsgBox “Date Service Completed not present” ’ Set the value in the current cell to “” Application.EnableEvents = False cell.Value = “” Application.EnableEvents = True End If End If Next cell
Exit Sub ’ Move this Exit Sub statement here End If
If Target.Offset(0, -5).Value <> “Service” Then MsgBox “Task is not a current Service” Application.EnableEvents = False Target.Value = “” Application.EnableEvents = True Exit Sub End If
If Target.Offset(0, -5).Value = “Service” And Target.Offset(0, 2).Value <> “” Then MsgBox “A New Service row will now be created” Target.Offset(0, -5).Value = “Serviced” Target.Offset(0, 1).Value = “” Dim newRow As Long newRow = Cells(Rows.Count, “B”).End(xlUp).Row + 1 Range(“B” & newRow).Value = “Service” Range(“C” & newRow).Value = Target.Offset(0, 2).Value 'column I dDate Completed Range(“F” & newRow).Value = Target.Offset(0, -1).Value 'column F Location Range(“J” & newRow).Value = Target.Offset(0, 3).Value 'column J Task Notes Range(“L” & newRow).Value = Target.Offset(0, 5).Value 'column L Task details Range(“H” & newRow).Value = Target.Offset(0, 2).Value + Target.Value 'column I + column G
Exit Sub End If End Sub
- Assistant
This VBA code is an event handler that runs when there is a change in the worksheet.
The code first declares some variable ranges and initializes them with specific ranges on the worksheet.
Then, it checks if the target range (the range that was changed) is within the range "I6:I506". If it is, it further checks if the value in the corresponding cell in column "G" is "Service" and if the target value is a date. If these conditions are met, it displays a message box and selects a different cell, depending on the condition. The exit sub statement is used to exit the event handler.
If the target range is within the range "G6:G506", the code iterates through each cell in the target range. If a cell value is not empty, it checks if the corresponding cell two columns to the right is empty. If it is, it displays a message box and sets the value of the current cell to an empty string. The exit sub statement is again used to exit the event handler.
Next, the code checks if the value in the cell five columns to the left of the target is not "Service". If it is not, it displays a message box and sets the target value to an empty string. The exit sub statement is used to exit the event handler.
Finally, if the value in the cell five columns to the left of the target is "Service" and the cell two columns to the right is not empty, it creates a new row in the worksheet and populates certain cells in the new row with values from the target and the offset cells. The exit sub statement is used to exit the event handler.