200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
A B C 1 Surname Name Nickname 2 Smith John Smithy 3 4
Surname, Name and Nickname are all dropdown list (Data Validation>List)
Nickname is dependant on Name (Indirect)
Name is dependant on Surname (Indirect)
Should Surname be deleted or replaced with another then
Name (and Nickname) should either be deleted or an error message should appear
Could somebody be of assistance to solve this problem
You could use something like this:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" And Target.Value = "" Then
MsgBox "Error: You can't change that value!"
End If
End SubChange $A$1 to the cell for the surname.
This takes care of a use deleting the option. It is goes into the worksheet module code section for the Sheet that has the drop downs.
To throw an error if the surname is changed when there are values in for the name and nickname, use something like this:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" And (Target.Value = "" Or _
(Target.Offset(, 1).Value <> "" And Target.Offset(, 2).Value <> "")) Then
MsgBox "Error: You can't change that value!"
End If
End Sub
This also works for the first scenario mentioned above.
This assumes the name cell is one ot the right of the surname cell and that that nickname is one to the right of the name cell. If that's not the case, change Offset(, 1) and Offset(, 2) accordingly.