Data validation

[wholesaleguy]

I have a data validation list that is based on another data validation list and everything is working fine. Is there a way to require that the second data validation be changed when the first one is changed so that the user will know to update the second data validation box. Maybe is could even display an error or blank out in someway to alert the user that the information in the first data validation box has changed, and you need to update the second one.

Any ideas?

Thanks in advance for you help.

 

[VoG]

Maybe this? http://www.contextures.com/xlDataVal02.html

 

[T. Valko]

I have a data validation list that is based on another data validation list and everything is working fine. Is there a way to require that the second data validation be changed when the first one is changed so that the user will know to update the second data validation box. Maybe is could even display an error or blank out in someway to alert the user that the information in the first data validation box has changed, and you need to update the second one.

Any ideas?

Thanks in advance for you help.

There are a couple of ways to do this.

You can use an event macro that clears the cell entry of the dependent list when the parent cell is changed, or you might be able to use conditional formatting to "hide" the entry in the dependent cell if it's not a valid entry for the parent entry.

It's probably easier to use the event macro.

 

[wholesaleguy]

an event macro seems like the most logical way to do this, but I am "stupid" when it come to how to do this. and advice. I will also start looking for help on the net.

 

[T. Valko]

an event macro seems like the most logical way to do this, but I am "stupid" when it come to how to do this. and advice. I will also start looking for help on the net.

Ok, let's assume the parent drop down list is in cell A2 and the dependent drop down list is in cell B2.

Navigate to the sheet in question
Right click the sheet tab
Select View Code
Copy and paste the code below into the window that opens:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo sub_exit
    If Target.Address = "$A$2" Then
    Range("B2").ClearContents
    End If
sub_exit:
Application.EnableEvents = True
End Sub

Close the window to return to Excel
Try it out.

 

[wholesaleguy]

That was exactly what I needed. With your code, and a few changes by me, I have the data validations working perfectly.

Thanks for you help.

 

[T. Valko]

That was exactly what I needed. With your code, and a few changes by me, I have the data validations working perfectly.

Thanks for you help.

You're welcome. Thanks for the feedback!

 

[wholesaleguy]

If you have time, can you describe the operations each line of code refers to? I am trying to understand what the code is doing. If you don't have the time, please don't worry about it. I am just thankful to have the info.

 

[T. Valko]

Ok, let's assume the parent drop down list is in cell A2 and the dependent drop down list is in cell B2.

Navigate to the sheet in question
Right click the sheet tab
Select View Code
Copy and paste the code below into the window that opens:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo sub_exit
    If Target.Address = "$A$2" Then
    Range("B2").ClearContents
    End If
sub_exit:
Application.EnableEvents = True
End Sub

Close the window to return to Excel
Try it out.

If you have time, can you describe the operations each line of code refers to? I am trying to understand what the code is doing. If you don't have the time, please don't worry about it. I am just thankful to have the info.

Ok, in very general terms...

Option Explicit
Explicitly declare any variables so there's no ambiguity.

Private Sub Worksheet_Change(ByVal Target As Range)
Defines the type of event, a change in a cell value. Target is the cell or cells in question.

Application.EnableEvents = False
Turns off all other events so that we don't go into an infinite loop

On Error GoTo sub_exit
If any errors occur stop execution of the procedure

If Target.Address = "$A$2" Then
Range("B2").ClearContents
End If
If there is a change in cell A2 then clear cell B2

sub_exit:
This is related to On Error GoTo sub_exit. If there is any error the execution of the procedure stops at the point of the error and bypasses any further instructions (lines of code) and goes to the code line sub_exit: and then restarts the procedure from that point forwards.

Application.EnableEvents = True
After we've done what we need to do we turn events back on.