Validation value cell update logic

[shelby_335]

Hi

In an excel sheet I have built some dynamic selection lists where the tier1 selection drives the tier2 & tier 3 available lists (tier 3 is a static list that has the same 7 entries for all tier 2 responses, which are also valid for all tier1 responses). At the moment only valid values for the tier2 field appear when tier1 is populated from a list.

I'm now trying to complete add some validation logic & automated corrections to update the sheet (tier2 & tier3 field) if and when the validation.value for tier2 is False. Is there a way to use the validation.value check to determine/select a cell and then update the tier3 value

Code I've been playing with attached below, the Active.Cell select statement picks the first cell in the defined range, not the cell detected as invalid, is there a way to move the select to the invalid data cell and loop down the range clearing all related tier3 values before clearing all invalid tier2 values

Sub DeleteInvalidData()
'Columns("M:M").Select
Range("M1:O20").Select
Dim C As Range
For Each C In Selection
'changes column 2 row 1 only
If C.Validation.Value = False Then ActiveCell.Select
ActiveCell.Offset(0, 2).Value = "Choose..."
'removes invalid entry values from range
If C.Validation.Value = False Then C.Value = ""
Next
End Sub

Thanks

[stnkynts]

This is your question

Is there a way to use the validation.value check to determine/select a cell and then update the tier3 value

The answer is yes, but explaining the answer is not that easy. Submit a copy of your workbook so we can show you/give you code to do what you need to do.

[Arkadi]

If I get it correctly C is the cell being tested? If so, then instead of

Please Login or Register  to view this content.

try

Please Login or Register  to view this content.

This will activate C rather than selecting the active cell, which is the cell active before the macro runs. (Or last activated cell)

[shelby_335]

VBA Coding Query.xlsm

Thanks - I had tried to attach the file but without success will try again.

I tried the logic suggested by Arkadi however it seems that the C.Validation.Value check does not trigger a cell selection and the initial range selection is maintained.

[stnkynts]

Note: This is not possible

Please Login or Register  to view this content.

Try this

Please Login or Register  to view this content.

[shelby_335]

Thanks for the advice on the not possible elements, I'm a VBA newbie/numpty so there's a few bits of code I've played with whilst trying to get this to work

I've tried porting the code into the sheet VBA code and am not able to make it work to help me understand let me paraphrase on the code you have provided

The first element of the code looks at the range in column A and if the field is blank then it deletes the target value in column C, however when I run the code I get a number of errors "Run-time error '1004' when column A is blank or set to "Choose..." from the row .Add Type

Attached v2 of the XLS with the code you supplied included

I'm not I understand sure how the code is supposed to work to check that the values in column C are valid compared to the value specified in column A and then reset column C & E to the value "Choose..." if the not valid state is detectedVBA Coding Query v2.xlsm

[stnkynts]

It works as follows:

1. You select a value from the data validation in column A.
2. That selection trigger the macro to populate the data validation in column C based upon the Named Ranges that you set up.
3. Here is the rub. I just noticed that your named ranges don't exactly match the values in the data validation options in column A. You need to change this.

I have amended to code to bypass if you delete the value in column A or change it to "Choose". You need to amend your named ranges to exactly match the selection in column A.
There are other ways to do it but this is quick and easy.

Please Login or Register  to view this content.