Setting excel validation with access vba (1 Viewer)

[Les Isaacs]

Hi All
We have an access routine that creates and populates an excel workbook, and adds lots of validation - typically:

1220      With objSheet.range("AM9:AM" & lngRowCount - 2).Offset(0, ColOffset).Validation
1230          .Add type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="1", Formula2:="11"
1240          .IgnoreBlank = False
1250          .InputTitle = "Pay period"
1260          .ErrorTitle = "Invalid period number"
1270          .ErrorMessage = "You must enter a period number between 1 and 11."
1280      End With

There is one particular column of data where I want the validation to check whether the value is exactly divisible by 473 - i.e. MOD 473 = 0. Can this be done with validation, and if so what would be on the .Add Type:= line?
Hope someone can help.
Many thanks
Les

 

[NBVC]

Try recording a macro of you creating the data validation. It should give you an idea of what the line would look like.

To do the data validation, you would select Custom and then enter the formula in the Source field... e.g. =MOD(A1,1)=0