Verify Data Validation

[davidbeach]

I have a workbook in which I make extensive use of data validation to limit user inputs. Some inputs can cause the validation parameters to change for other inputs. I can check to see if any data validation criteria are violated by clicking on the "Circle Invalid Data" button on the Formula Auditing toolbar. What I'd like to be able to do is have a VBA routine that will check each sheet of the workbook for invalid data and take to use to the invalid data for a correction. The help files don't seem to be of much help, or I don't know what to look for. Any ideas?

 

[pmover]

davidbeach,

a thought to ponder . . . have you tried using range names for each data validation source and then referencing the range name when defining the cell data validation? to further expand, "dynamic arrays" are easily created and may be suitable for your needs. the "dynamic arrays" can change depending upon user inputs. a search for dynamic arrays via google should suffice.

fyi, a preference of mine fully use the standard excel features to avoid writing vba code. however, there are occasions in which the std XL features/capabilities will not meet the needs.

just a thought and good luck!
-pmover

 

[electricpete]

For one thing, if validation is set up right, it is very difficult for user to bypass and enter the wrong value.

But if you want to check whether cell D3 validation criteria is set, look at
sheet1.Range("D3").Validation.Value

If true, your criteria are met, if not they are not met.
sheet1.Range("D3").Validation has many other properties and methods - there are various ways you can find out what is availalbe from within vba editor. One way I do it is type
? sheet1.Range("D3").Validation.
in the vba immediate window. After I type the last period, a list of values appear. There is also the object browser... I still haven't quite got the hang of that.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

I also agree with previous comment, it would be a good idea to assign a name to each input cell.

Then you can loop through the name collections as easy way to examine the status of each item. The following loop should look at all your ranges and print out to immediate window the names of ranges which do not meet validation. Of course you probably want to do something other than just printing names.

Dim myname As Name
For Each myname In activeworkbook.Names
if myname.RefersToRange.Validation.Value=false
debug.print myname.name
endif
Next myname


=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[davidbeach]

Thanks, you've given me some things to work with.

True, it is very difficult to enter data that doesn't meet the validation criteria, but it is easy to modify the validation criteria after the fact. The spreadsheet in question is used to generate relay settings and one setting may have validation criteria that depend on the value of another setting. So A is filled in correctly, but then when B is filled in it changes the acceptable values of A and possibly makes the previously valid response in A now invalid.

All cells with data validation do have range names or soon will.

 

[davidbeach]

I'll hijack my own thread and ask a new, separate but related question (data validation anyway).

In the List option of Data Validation, you can type in a comma delimited list, or you can use a formula to select multiple lists where each list is a named range. What I'd like to do, if possible, is use a formula to select lists that are included as part of the formula. Is there a way of delimiting a string so that excel will recognize it as a list?

 

[electricpete]

In the List option of Data Validation, you can type in a comma delimited list, or you can use a formula to select multiple lists where each list is a named range.

I have heard of using comma delimited list (directly in the validation / list dialogue box.
I have heard of using named range with name entered into validation / list dialogue box.
I have heard of using a formula (custom). If it evaluates to true then the value is accepted. (But it doesn't provide a pull-down list to select from.)
I haven't heard of how to do "you can use a formula to select multiple lists where each list is a named range". Not sure how that can be done but I'd be interestd to hear.

Then the "formula to select lists that are included as part of a formula" is getting more confusing. Are you looking to use the custom option and build a true/false formula? I imagine such a formula could incorporate simple vba functions that do whatever you want in terms of returning true/false depending on any conditions you want... but again with custom option there is no pulldown.

Another more complicated (for programming) but more versatile option to keep in mind could be combobox if data validation is not giving the funtionality you want.



=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[davidbeach]

Where the number of choices is limited in scope and reasonable in length, I'm trying to provide a drop down list via the Data Validation List function. I've used lots of Data Validation Custom functions where the values are, for instance, a range of numbers or the word "OFF".

=OR(Z1P="OFF",AND(Z1P>=0.05,Z1P<=64))

is an example of a custom function.

If I create ranges of the possible choices I can select multiple lists such as

=CHOOSE(Relay_Type_2,
CHOOSE(PORT32,Proto_311C_AB,Proto_311C_A,Proto_311C_B,Proto_311C_N),
CHOOSE(PORT32,Proto_311L_AB,Proto_311L_A,Proto_311L_B,Proto_311L_N),
CHOOSE(PORT32,Proto_421_AB,Proto_421_A,Proto_421_B,Proto_421_N))

one of the more complex examples. That one I'd probably not want to include all of the lists enumerated in the formula (probably too long that way and certainly too complex).

A much simpler case though, might need to select between "N" and "N,2,3,3R" as valid choices. Easy to write a custom, easy to name a couple of ranges, but the workbook has many hundreds of named ranges already and I only need that validation criteria for one cell.

 

[yakpol]

David,
In addition to data validation you can use conditional formatting. For example, colour a cell red when data in it does not meet requirements.

Yakpol

 

[davidbeach]

Lots of conditional formatting already in use. The values of all the cells with data validation are required to have valid data for the export file to be written without errors and imported into a protective relay without errors. Earlier versions have used conditional formatting to flag inputs that needed to be revised but users would not make the revisions.

 

[yakpol]

As you suggested it's doable in VBA. Check each input cell for validity, if it does not pass select the cell and issue the warning text.

Range("Cell1_to_check").Select
MsgBox "Data in the cell " & Range("Cell_to_check").Address & " is invalid, shall satisfy conditions...."
End

You will need a command button to execute validation code.

 

[electricpete]

Sounds like you want to make it bulletproof and you have less than cooperative users.

If you structure the spreadsheet so that exporting data to relay-readable format relies on one of your marcos, then you could force a validation check at that export step (along with earlier validations which might be triggered by change events or button pushes). Otherwise users might not push the validate button or might disable macros or whatever.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

One more thing - since the example you posted has a dependent nature to it (value of RelayType2 changes available list), there is a stategy for forming dependent dropdown lists here:

http://www.contextures.com/xlDataVal02.html

It relies heavily on values stored in named ranges which you said you wanted to avoid. Of course those named can be stored in another worksheet that is hidden and doesn't clutter the user-visible workspace. Standardized naming convention for your validation lists would also help if htere is a large number.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[yakpol]

David,
On a few occasions I used the following technique for data validation. Insert a worksheet containining a table with the references to all input cells in the workbook. In each row it will have cell address, value, result of validation (TRUE or FALSE), and text of the warning message.
To retrieve appropriate warning message use reference functions to find a message corresponding to the first occurence of FALSE in the validation results.

My explanations may be a bit confusing, see attached file to clear it up.