Creating excel validation from access

[Les Isaacs]

Hello All
I have a module in an accdb that creates a complex excel file, and as part of this a lot of validation rules are applied to certain columns, e.g.

        'Stop if Joining NHSP is invalid date
2010    With objSheet.range("W" & lngRowCount + 1).Validation
2020        .Add type:=xlValidateDate, AlertStyle:=xlValidAlertStop, Operator:=xlGreater, Formula1:="1/1/1997"
2030        .IgnoreBlank = True
2040        .InputTitle = ""
2050        .ErrorTitle = "Invalid Date"
2060        .InputMessage = ""
2070        .ErrorMessage = "Please enter a valid date, in the format dd/mm/yy, after 01/01/97"
2080    End With

I now need to add a validation rule to check for a valid email address, but I'm not sure what to put on the .Add type line. I think the only restriction on the rule will be that the data must contain a '@', and possibly also a 'dot' (unless anyone knows of any tighter restriction for email addresses), but how do I represent this with .Add type, Operator and Formula1?

Hope someone can help.
Many thanks
Les

 

[CJ_London]

You could try

instr(EmailName,"@") + instr(EmailName,".")<>0

 

[Les Isaacs]

Hi CJ_London

Thanks for your reply.

I did think that instr might be involved, but had two problems:

1. Strangely, although I have Office 2010 on my PC, the instr function seems not to be available. If I type =instr("Hello","e") into a cell, I get #Name. In the list of Text formulas (from the Formulas menu item) instr isn't there - but 'Search' is, and if I type =SEARCH("e","Hello") into a cell, I get 2 . I've no idea why instr isn't available;
2. I wasn't sure how to use the instr function (or 'Search') with the .Add type, Operator and Formula1 parameters in my VBA. What would I have in place of .Add type:=xlValidateDate, AlertStyle:=xlValidAlertStop, Operator:=xlGreater, Formula1:="1/1/1997"

I'd be extremely grateful for any ideas

Thanks again for any help.
Les

 

[CJ_London]

Sorry, thought you were doing this in VBA

If you are doing it by adding a calcualtion to an excel field then I would refer you to this link

http://forums.tutorialized.com/ms-excel-97/in-excel-how-to-search-a-text-string-get-true-5215.html

Suggest the Search function is the one you would want

 

[Les Isaacs]

Hi CJ_London

Now I'm really confused:

I am doing this in VBA. The VBA is in an access module. The sub in the access module creates the excel file, and sets the validation for lots of columns - like the example I gave:

        'Stop if Joining NHSP is invalid date
2010    With objSheet.range("W" & lngRowCount + 1).Validation
2020        .Add type:=xlValidateDate, AlertStyle:=xlValidAlertStop, Operator:=xlGreater, Formula1:="1/1/1997"
2030        .IgnoreBlank = True
2040        .InputTitle = ""
2050        .ErrorTitle = "Invalid Date"
2060        .InputMessage = ""
2070        .ErrorMessage = "Please enter a valid date, in the format dd/mm/yy, after 01/01/97"
2080    End With

So now I need to add more VBA to the access sub, similar to lines 2010 to 2070 above, to set the validation for the email column in the excel file. After the excel file has been created (with all its validation etc.) it's sent to the user, who will then add data to it - which needs to be validated!

Hope that explains it better

I'm also at a loss as to why the Instr function seems not to be available when I open excel

Thanks again - hope you can help
Les

 

[CJ_London]

I'm also at a loss as to why the Instr function seems not to be available when I open excel

Instr is a VBA and SQL function, not an Excel function. If you want to use it in Excel you need to use it within a macro or module

with

With objSheet.range("W" & lngRowCount + 1).Validation

You are using VBA to set values/properties in an Excel object so these need to be excel values/properties.

Suggest the way to determine the correct code would be to to the following.

1. Open Excel
2. Start a macro and build the validation you require (should be a custom one)

Looking at the code should then produce the relevant line something like

 .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= xlBetween, Formula1:="=Search(A2,""@"")<>0"

which you will then need to modify to meet your requirements - sorry, don't know how to do this to meet your requirements (presumably substitute A2 with ("W" & lngRowCount + 1, but only guessing)