execute assigned data validation in vba?

  • Thread starter Thread starter cate
  • Start date Start date
C

cate

I'm using a form to collect information and then populating cells on a
ws. These cells have data validation definitions which were assigned
during the sheet's construction. Is there a way I can execute these
data validations? When I write the cell's value property from vba,
with a value I know is outside the validation limits, I get no error.

Thank you
 
cate laid this down on his screen :
I'm using a form to collect information and then populating cells on a
ws. These cells have data validation definitions which were assigned
during the sheet's construction. Is there a way I can execute these
data validations? When I write the cell's value property from vba,
with a value I know is outside the validation limits, I get no error.

Thank you
Click to expand...

I use validation in the userform via a TextBox_AfterChange event, where
I can direct the user back to the control with the input error.

Validation is a dropdown list (populated by a named range *or* manually
created list) that I read into a string variable for checking user
input via the InStr() function.

In this case I don't require DV in the wks.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Oops.., I meant TextBox_AfterUpdate event!

--
Garry

Free usenet access athttp://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion
Click to expand...

Yes, I can validate data in the form any way I want, but the user's
notion of what is or is not valid could change. He owns the sheet.
It is important that I use their validation rules.

Thanks for the input.
 
cate presented the following explanation :
Yes, I can validate data in the form any way I want, but the user's
notion of what is or is not valid could change. He owns the sheet.
It is important that I use their validation rules.

Thanks for the input.
Click to expand...

So why can't you use their validation rules? Are they not listed
somewhere accessible to VBA?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Cate,

Garry's right, there are ways to access the validation rules. Here is a skeleton framework to generate a message box with the basic rule values for an active cell. It is very rough, but illustrates the concept. The macro starts by trying to find the validation type. An error means there is no validation rule on the active cell.

Next, it will find the type of rule on a list (search help for Validation.Type to see more details). Depending on the type of rule, there are three values that may be of interest: Operator, Formula1 and Formula2. The operator returns a number corresponding to a list of operators (1 = "between", 2 = "not between", etc.). I have included a function to find the operator based on this value. The Formula1 and Formula2 fields contain either values or formulas (such as low and high numbers or custom lists/formulas).

You will have to do a lot of customizing to have your macros work with this.. For example, you'll probably want to change this sub to a function that returns a boolean. In that case, you would include the value to check as apassed variable and use the "Select Case .Type" section to evaluate this value against the type of validation rule.

There may be an easier way, but the method below may work in a pinch.

Ben

Sub GetValidation()
Dim sMessage As String

On Error Resume Next
Debug.Print ActiveCell.Validation.Type

If Err.Number = 0 Then 'Cell has validation
On Error GoTo 0
With ActiveCell.Validation
Select Case .Type
Case 0 'Validate only when user changes the value
Case 1 'Whole numeric values
sMessage = "Whole numeric values " & _
GetOperator(.Operator) & " " & .Formula1 & " " & .Formula2
Case 2 'Numeric values
sMessage = "Numeric values " & _
GetOperator(.Operator) & " " & .Formula1 & " " & .Formula2
Case 3 'List
sMessage = "List of values: " & .Formula1
Case 4 'Date
sMessage = "Dates " & _
GetOperator(.Operator) & " " & .Formula1 & " " & .Formula2
Case 5 'Time
sMessage = "Times " & _
GetOperator(.Operator) & " " & .Formula1 & " " & .Formula2
Case 6 'Text Length
sMessage = "Text Length " & _
GetOperator(.Operator) & " " & .Formula1 & " " & .Formula2
Case 7 'Formula
sMessage = "Formula: " & .Formula1
End Select
End With
Else
'No validation rules present
sMessage = "No rules present"
Err.Clear
End If
MsgBox sMessage
End Sub

Function GetOperator(lType As Long) As String

Select Case lType
Case 1
GetOperator = "Between"
Case 2
GetOperator = "Not Between"
Case 3
GetOperator = "Equal To"
Case 4
GetOperator = "Not Equal To"
Case 5
GetOperator = "Greater Than"
Case 6
GetOperator = "Less Than"
Case 7
GetOperator = "Greater Than or Equal To"
Case 8
GetOperator = "Less Than or Equal To"
Case Else
GetOperator = vbNullString
End Select

End Function
 
One other you may try is to check whether the destination cells contain valid data after populating them. You would use:

Range("A2:A5").Validation.Value

This formula returns TRUE if all cells in the range contain valid data and FALSE if any of the cells contain invalid data. You could, for example, have your userform populate a range of cells, then check whether the data is valid. If not, have the code undo the changes and alert the user.

As for generating the message to the user, you could adapt the code from myearlier post to create an informational message. By turning the GetValidation Sub into a Function, you could loop through each cell in the destination range and build an error message for each invalid entry.

Hope this helps,

Ben
 
I was thinking of something similar to how message rules are managed in
an email app. I typically use a similar mechanism wherein the DV rules
are stored in a text file OR hidden sheet within the wkb. The latter
approach serves better for handling file-specific rules, where the text
file approach is more used for handling DV rules on a global scope.

Either way, it's not going to be a trivial task to implement a DV rules
management system that's beyond a simple list of single values per
field.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Ben,
In this scenario I think the data is managed entirely in the form. Cate
doesn't say whether it's the builtin DataForm or a userform. I use the
latter and read/write a protected sheet (hidden, or not). This way I
have full control over the input.

Another consideration is what control are being used to expose the
data to users. If a data control that's *bound* it probably enforces DV
as defined in the database. If that's a spreadsheet then DV has to be
managed by code. At the end of the day it's a giant PITA at best, but
well worth the effort in the long run.

What disturbs me is that Cate infers the user makes up the DV rules as
they go. Scary!<g>

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Ben,
In this scenario I think the data is managed entirely in the form. Cate
doesn't say whether it's the builtin DataForm or a userform. I use the
latter and read/write a protected sheet (hidden, or not). This way I
have full control over the input.

Another consideration is what control are being used to expose the
data to users. If a data control that's *bound* it probably enforces DV
as defined in the database. If that's a spreadsheet then DV has to be
managed by code. At the end of the day it's a giant PITA at best, but
well worth the effort in the long run.

What disturbs me is that Cate infers the user makes up the DV rules as
they go. Scary!<g>

--
Garry

Free usenet access athttp://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion
Click to expand...


To all, thank you.
I started to dig around in the validation obj... too much.
However, testing for valid data is a nice suggestion. Will be using
that. Thanks again.
 
cate wrote on 08/11/2012 :
Ben,
In this scenario I think the data is managed entirely in the form. Cate
doesn't say whether it's the builtin DataForm or a userform. I use the
latter and read/write a protected sheet (hidden, or not). This way I
have full control over the input.

Another consideration is what control are being used to expose the
data to users. If a data control that's *bound* it probably enforces DV
as defined in the database. If that's a spreadsheet then DV has to be
managed by code. At the end of the day it's a giant PITA at best, but
well worth the effort in the long run.

What disturbs me is that Cate infers the user makes up the DV rules as
they go. Scary!<g>

--
Garry

Free usenet access athttp://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion
Click to expand...


To all, thank you.
I started to dig around in the validation obj... too much.
However, testing for valid data is a nice suggestion. Will be using
that. Thanks again.
Click to expand...


Best wishes in your endeavors...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Back
Top