Please write me an Excel 365 VBA code that will detect if cell C4 is changed. If cell C4…
- 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"
- Assistant
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "4" 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.
- User
The below text shows up as red color when i paste it into the VBA editor... why is that?
If Target.Address = “4” Then If Target <> “” And Target <> “Staff Group” Then MsgBox “Try again”
- 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 = "4" 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.
- User
can you add another function to that code that also check cell R4 for the same change?
- 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 SubThis code checks whether the change occurred in either C4 or R4, using the
Orlogical 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 theValueproperty is used to check the cell contents, instead of using the cell object directly (Target), for better performance. - User
generate that code again, i think you made a mistake
- 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 SubThis code checks whether the change occurred in either cell C4 or R4 using the
Oroperator. 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.
- User
That code is not working at all
- 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 SubThis code checks whether the change occurred in either cell C4 or R4 using the
Oroperator. 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.
- User
I need to code to be copy/pasteable, but you are using the wrong quotation symbols
- 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 SubPlease try this updated code and let me know if it works for you.