VBA help doing data validation

[kcgojnur]

Hi -

I'm looking to see if there is a way to automatically update cells based on a selection. In cell C6 I have 4 options to choose from using data validation. If a selection of EE_only or EE+Child(ren) I would like to have cell C8 go to "N/A". However, cell C8 is also using data validation. Can anyone offer any suggestions?

Here is the layout
C6: has the options of EE_Only, EE+Spouse, EE+Child(ren), EE+Family through data validation
C7: has the option of Yes or no which is through data validation
C8: has the option of yes or no which is also through data validation. This cell is provided for those selecting EE+Spouse or EE+Family. I'm looking to have this cell update to "N/A" if EE_Only or EE+Child(ren) is selected.

Any help/suggestions is greatly appreciated.

[Arkadi]

In the project browser, double-click on the sheet that you want this behaviour in, and paste the following code:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
If Not Intersect(Target, Range("C6")) Is Nothing Then
    If Intersect(Target, Range("C6")).Value = "EE_Only" Or Intersect(Target, Range("C6")).Value = "EE+Child(ren)" Then Range("C8") = "N/A"
End If
Application.EnableEvents = True

End Sub

[Arkadi]

Additionally, you could also force C8 to be "N/A" even if the user tries to change it to yes or no after selecting one of EE_Only or EE+Child(ren):

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
If Not Intersect(Target, Range("C6")) Is Nothing Then
    If Intersect(Target, Range("C6")).Value = "EE_Only" Or Intersect(Target, Range("C6")).Value = "EE+Child(ren)" Then Range("C8") = "N/A"
End If
If Not Intersect(Target, Range("C8")) Is Nothing Then
    If Range("C6").Value = "EE_Only" Or Range("C6").Value = "EE+Child(ren)" Then Range("C8") = "N/A"
End If
Application.EnableEvents = True

End Sub

[kcgojnur]

Thanks for the help! What is meant by project browser?

[Arkadi]

When in excel, press Alt-F11 and that brings you to the macros... on the left you will have a window at the top with sheet1, sheet2, etc and also "ThisWorkbook". That is where you click on the sheet that has the cells you want to change, and paste the code I provided. I guess I should have said Project Explorer not Project Browser.

Project_Explorer.gif

[kcgojnur]

Hi so I pasted the code in "ThisWorkBook", but it doesn't appear to be working. I even went to the sheet listed and pasted, but didn't work. Any suggestions?

[kcgojnur]

Got it! Please disregard, and thanks.

[Arkadi]

As i said... Not in thisworkbook... Do it on the sheet you are working with

[kcgojnur]

Hi Arkadi -

I was wondering if you can help me add the following to what you have already done? If cell C4 = "Union" than cell C5 and C6 automatically goes to "N/A".