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

Please write me an Excel 365 VBA code that will detect if cell C4 is changed. If cell C4…

  1. User

    Please write me an Excel 365 VBA code that will detect if cell C4 is changed. If cell C4 contains anything other than a blank or "Staff Group" then display a message that says "Try again"

  2. Assistant

    Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "CC4" Then If Target <> "" And Target <> "Staff Group" Then MsgBox "Try again" End If End If End Sub

    Note: This code should be placed in the worksheet module of the sheet where Cell C4 is located.

  3. User

    The below text shows up as red color when i paste it into the VBA editor... why is that?

    If Target.Address = “CC4” Then If Target <> “” And Target <> “Staff Group” Then MsgBox “Try again”

  4. Assistant

    The text shows up in red color because of the use of curly quotation marks instead of straight quotation marks. In VBA, straight quotation marks (") are used to delimit string literals, whereas curly quotation marks (“ ”) are not recognized and may cause a syntax error.

    To fix the issue, you can replace the curly quotation marks with straight quotation marks like this:

    If Target.Address = "CC4" Then If Target <> "" And Target <> "Staff Group" Then MsgBox "Try again" End If End If

    By using the correct quotation marks, the code will be recognized and executed without errors.

  5. User

    can you add another function to that code that also check cell R4 for the same change?

  6. Assistant

    Yes, here's an updated code that checks for changes in both C4 and R4:

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = "$C$4" Or Target.Address = "$R$4" Then
            If Target.Value <> "" And Target.Value <> "Staff Group" Then
                MsgBox "Try again"
            End If
        End If
    End Sub
    

    This code checks whether the change occurred in either C4 or R4, using the Or logical operator. It also checks the value of the changed cell against an empty string or the string "Staff Group", and displays a message box if it doesn't match. Note that the Value property is used to check the cell contents, instead of using the cell object directly (Target), for better performance.

  7. User

    generate that code again, i think you made a mistake

  8. Assistant

    I apologize if there was a mistake in my previous response. To check for changes in both C4 and R4, you can use the following code:

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = "$C$4" Or Target.Address = "$R$4" Then
            If Target.Value <> "" And Target.Value <> "Staff Group" Then
                MsgBox "Try again"
            End If
        End If
    End Sub
    

    This code checks whether the change occurred in either cell C4 or R4 using the Or operator. It then checks whether the new value entered in the cell is not blank and not "Staff Group". If the condition is true, it displays a message box with the text "Try again".

    Please let me know if you have any further questions or concerns.

  9. User

    That code is not working at all

  10. Assistant

    I apologize if the previous code didn't work for you. Here's a revised version of the code that should work:

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = "$C$4" Or Target.Address = "$R$4" Then
            If Not (IsEmpty(Target)) And (Target <> "Staff Group") Then
                MsgBox "Try again"
            End If
        End If
    End Sub
    

    This code checks whether the change occurred in either cell C4 or R4 using the Or operator. It then checks whether the new value entered in the cell is not blank and not "Staff Group". If the condition is true, it displays a message box with the text "Try again".

    Please test this code and let me know if it works for you or if you have any further questions.

  11. User

    I need to code to be copy/pasteable, but you are using the wrong quotation symbols

  12. Assistant

    I apologize for the mistake in using the wrong quotation symbols. Here's the updated code which uses only the standard straight quotation marks that can be directly copy-pasteable:

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = "$C$4" Or Target.Address = "$R$4" Then
            If Not (IsEmpty(Target)) And (Target.Value <> "Staff Group") Then
                MsgBox "Try again"
            End If
        End If
    End Sub
    

    Please try this updated code and let me know if it works for you.