Error message based on value of another cell

0

  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

Answer
Discuss

Discussion

Thank you. I am going to try out the second answer
Siyas Aug 4, '16 at 12:12 pm
Ok, let me know if it works and if it does don't forget to select the answer!
don (rep: 1297) Aug 4, '16 at 1:26 pm
Add to Discussion

Answers

0

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 Sub

Change $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.

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login