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

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 at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Nov 7, 5:44*pm, GS wrote:
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

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.

On Nov 7, 7:23*pm, cate wrote:
On Nov 7, 5:44*pm, GS wrote:

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

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.

Guess not:
http://social.msdn.microsoft.com/For...8-28bf228e0f29

cate presented the following explanation :
On Nov 7, 5:44*pm, GS wrote:
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

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.

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 a passed 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