Is there a way to update a cell without implementing a Worksheet_Change?

[USAMax]

I have four cells that are are dependent of each other using Data Validation. When I change the cell in column C it updates the pull down list in column D. This continues through F.

The problem is that if I have all of this information filled in and I decide to start over by changing the cell in column C, it may change the pull down list in column D but it still shows the last value I selected that is unrelated to what I just selected in column C.

I need to clear the next column after it updates the drop down list but that triggers the Worksheet_Change. I've tried ClearContents but that still triggers the Worksheet_Change.

 

[pgc01]

Hi

I don't understand well what you mean.

If your dropdowns are, for ex., in C1:F1 then if a cell in C1:E1 changes you should clear the cells to the right up to F1.

For ex., if D1 changes you should clear E1 and F1.

I'd use the worksheet change event.

Private Sub Worksheet_Change(ByVal Target As Range)

' just for cells C1:E1
If Not Intersect(Target, Range("C1:E1")) Is Nothing Then
    Application.EnableEvents = False
    Range(Target.Offset(, 1), "F1").ClearContents
    Application.EnableEvents = True
End If
End Sub

 

[USAMax]

Here is an example, lets say I sell all kinds of transportation and I choose the following:

C8 = Cars
D8 = VW
E8 = TOUAREG
F8 = Black

Now I decide that is not what I want and I change C8 to Trucks. It would look funny to have a Black VW TOUAREG Truck. Because of this I will either clear the cell to the right or update it with the first related option depending on the situation. The problem is that when I change anything in C through F it triggers the Worksheet_Change.

 

[pgc01]

The problem is that when I change anything in C through F it triggers the Worksheet_Change.

I don't understand why you say that that's a problem. You can use the worksheet_change() to clear the cells to the right, which I understand is what you need.

Did you test my code for C1:F1?

If instead of clearing the cells to the right you prefer to write the first related option, replace the ClearContents statement with the statements to write those options in the cells.

Anyway, try testing the code as is, for C1:F1.

Or maybe I'm not understanding what you need?

 

[USAMax]

Thank you PGC, I looked at all of your code but I did not see the part I needed, the "Application.EnableEvents = False"

I haven't used it since school and that seams like a life time ago.

Thank you!

 

[FDibbins]

1 way to do this is to use Conditional Formatting, with a fomrmula IN the that tests the contents against the list that *should* be avaiable, and then change the color (to red?) if the contents doesnt match?

No VBA needed

 

[pgc01]

I'm glad it helped. Thanks for the feedback.