Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· openaiHugging Face Suzhen/CodeChat

I would like to create two differnet VBA events that do the following; If I insert a…

  1. 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

  2. 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 Sub
    

    To 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.

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