I would like to create two differnet VBA events that do the following; If I insert a…
- User
I would like to create two differnet VBA events that do the following;
If I insert a date in column I then of the same row check If column B has a text value 'Service'. If colulmn B value is not 'Service', then pop up the message 'This is not a current Service" and Exit Sub If column B value is 'Service' Then pop up message "Thank you for entering Service Date Completed. Please proceed to enter Service Schedule as number of days". and end this Sub
If I insert a number value in column G then of the same row check If column I has a date value and check if column B on the same row has a text value "Service" If colulmn B value is not 'Service', then pop up the message 'This is not a current Service" and Exit Sub If column I value is not a date value Then pop up message "Date Service Completed not present" and Exit Sub If all conditions are OK, then on the same row where I inserted a number in column G, enter a date value into column H that is equal to the date value of 'column I plus the number value in column G' (I+G) Then change the value of column B on the same row to the text value 'Serviced'. and then pop up message "A New Service row will now be created" Then find the next available empty row in the range B:J and change the cell values of the new row as described; Column B value = 'Service' Column C value = Date Value displayed in column I from the row where column G was recently changed Column E value = Date Value dispalyed in column H from the row where column G was recently changed Column J value = Value dispalyed in column J from the row where column G was recently changed Column L value = Value dispalyed in column L from the row where column G was recently changed and end this sub
- Assistant
Here is an example code that accomplishes your requirements:
Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim cell As Range Set rng = Intersect(Target, Range("I:I")) ' Check for changes in column I If Not rng Is Nothing Then For Each cell In rng If IsDate(cell.Value) Then ' If a date is inserted in column I If cell.Offset(0, -7).Value <> "Service" Then ' Check if column B is "Service" MsgBox "This is not a current Service" Exit Sub Else MsgBox "Thank you for entering Service Date Completed. Please proceed to enter Service Schedule as number of days." Exit Sub End If End If Next cell End If Set rng = Intersect(Target, Range("G:G")) ' Check for changes in column G If Not rng Is Nothing Then For Each cell In rng If IsNumeric(cell.Value) Then ' If a number is inserted in column G If IsDate(cell.Offset(0, 2).Value) = False Then ' Check if column I has a date value MsgBox "Date Service Completed not present" Exit Sub ElseIf cell.Offset(0, -3).Value <> "Service" Then ' Check if column B is "Service" MsgBox "This is not a current Service" Exit Sub Else Dim newDate As Date newDate = cell.Offset(0, 2).Value + cell.Value ' Calculate the new date cell.Offset(0, 3).Value = newDate ' Update column H with the new date cell.Offset(0, -3).Value = "Serviced" ' Update column B with "Serviced" MsgBox "A New Service row will now be created" Dim newRow As Long newRow = Cells(Rows.Count, "B").End(xlUp).Row + 1 ' Find the next available empty row Range("B" & newRow).Value = "Service" ' Update column B of the new row Range("C" & newRow).Value = cell.Offset(0, 2).Value ' Update column C of the new row Range("E" & newRow).Value = newDate ' Update column E of the new row Range("J" & newRow).Value = cell.Offset(0, 5).Value ' Update column J of the new row Range("L" & newRow).Value = cell.Offset(0, 7).Value ' Update column L of the new row Exit Sub End If End If Next cell End If End SubTo use this code, you need to open the Visual Basic Editor in Excel by pressing ALT+F11. Then, insert a new module and paste the code into the module. This code will run whenever there is a change in column I or G in the worksheet.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.