CODEWITHSUNDEEP
excelvba
Shawn V. Wilson
Shawn V. Wilson

Reputation: 1111

How make a macro trigger a data validation warning?

I have a table with a column that should only have one of 14 possible entries, so I applied data validation to it.

I have a macro that adds to the column. It currently adds only the 14 entries, but in case of changes I'd like the data validation to be enforced when the code adds an illegal entry.

But it doesn't. If the code enters an illegal entry, it doesn't throw an error.

I'm probably forced to write more code that validates every entry, but I like the idea of the spreadsheet (outside of any macros) watching over the code.

Is it possible for a macro to react to a Data Validation warning?

Upvotes: 1

Views: 1441

Answers (2)

PeterT
PeterT

Reputation: 8557

This goes along with Mathieu's answer as a bonus. I've often used the following function just to check and make sure a cell/range has validation before accessing the .Validation object of the range because you can get an error if it's been turned off (or you have the wrong range to begin with).

Option Explicit

Private Function HasValidation(ByRef theRange As Range, _
                               Optional ByVal validationType As XlDVType = xlValidateList) As Boolean
    On Error Resume Next
    Dim cell As Range
    For Each cell In theRange
        Dim vType As XlDVType
        vType = cell.Validation.Type
        If Err.Number > 0 Then
            '--- no validation at all, so exit!
            HasValidation = False
            Exit For
        End If

        If vType = validationType Then
            HasValidation = True
            '--- keep going in case we find a cell that
            '    doesn't have validation
        Else
            HasValidation = False
            Exit For
        End If
    Next cell
End Function

Upvotes: 1

Mathieu Guindon
Mathieu Guindon

Reputation: 71157

You can use Worksheet.CircleInvalid to show validation errors, and Worksheet.ClearCircles to clear them.

That won't prevent code from entering invalid data or throw any errors, but at least you get a reaction to data validation errors.

If you want an error to be raised upon entering invalid data, you need to raise the error yourself, using Range.DataValidation.Value which will be False if the cell contains an invalid value:

ws.Cells(1, 1).Value = 42 'validation allows integers between 1 and 10
If Not ws.Cells(1, 1).Validation.Value Then 
    Err.Raise ERR_INVALID_DATA, "DataValidation", "Value is not valid for this cell."
End If

Upvotes: 3

Related Questions