I've posted about this error before, but no one can seem to figure it out.
I'm validating the input in a spreadsheet that is created by VBA. The
validation criteria works just fine when I enter it into the custom
validation box:
=AND(LEN(B7)=7,ISNUMBER(MID(B7,2,6)*1),CODE(LEFT(UPPER(B7),1))>64,CODE(LEFT(UPPER(B7),1))<91)

When I put that same forumla into VBA, as validation criteria, it looks
like:

..Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=AND(LEN(B7)=7,ISNUMBER(MID(B7,2,6)*1)
,CODE(LEFT(UPPER(B7),1))>64,CODE(LEFT(UPPER(B7),1))<91)"

This line gives a Application defined or object defined error. I've run the
first term (LEN(B7)=7 separately, and it runs fine. But the second term,
ISNUMBER(MID(B7,2,6)*1)
,CODE(LEFT(UPPER(B7),1))>64,CODE(LEFT(UPPER(B7),1))<91)

doesn't run. So does anyone see what is causing the problem?
Thanks in advance!

a few things:

It probably just copied poorly into the newsgroup, but your formula is all
on one line in VB?

Is there any validation already in cell? I get the same error message when
this occurs. You could add something like

ActiveCell.Validaition.Delete

to clear out any previous validation.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"salgud" wrote:

> I've posted about this error before, but no one can seem to figure it out.
> I'm validating the input in a spreadsheet that is created by VBA. The
> validation criteria works just fine when I enter it into the custom
> validation box:
> =AND(LEN(B7)=7,ISNUMBER(MID(B7,2,6)*1),CODE(LEFT(UPPER(B7),1))>64,CODE(LEFT(UPPER(B7),1))<91)
>
> When I put that same forumla into VBA, as validation criteria, it looks
> like:
>
> ..Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
> xlBetween, Formula1:="=AND(LEN(B7)=7,ISNUMBER(MID(B7,2,6)*1)
> ,CODE(LEFT(UPPER(B7),1))>64,CODE(LEFT(UPPER(B7),1))<91)"
>
> This line gives a Application defined or object defined error. I've run the
> first term (LEN(B7)=7 separately, and it runs fine. But the second term,
> ISNUMBER(MID(B7,2,6)*1)
> ,CODE(LEFT(UPPER(B7),1))>64,CODE(LEFT(UPPER(B7),1))<91)
>
> doesn't run. So does anyone see what is causing the problem?
> Thanks in advance!
>

I got it to work very simply.

1) I copied your formula from th epsting not including the equal sign or the
double quotes and pasted th estring into notepad. Then I made the formula
one line with not spaces in the line.

2) I went to the worksheet and turned on Macro recorder.
3) I went to cell I7 and manual created a validation list. went to data
validation custom. Then I pasted the formula from notepad into the formula
box and pressed oK. Here is the formula I got

With Selection.Validation
.Delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:= _

"AND(LEN(B7)=7,ISNUMBER(MID(B7,2,6)*1),CODE(LEFT(UPPER(B7),1))>64,CODE(LEFT(UPPER(B7),1))<91)"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With


You code is incorrect. You have the equal sign in the formula which isn't
needed in VBA.


"salgud" wrote:

> I've posted about this error before, but no one can seem to figure it out.
> I'm validating the input in a spreadsheet that is created by VBA. The
> validation criteria works just fine when I enter it into the custom
> validation box:
> =AND(LEN(B7)=7,ISNUMBER(MID(B7,2,6)*1),CODE(LEFT(UPPER(B7),1))>64,CODE(LEFT(UPPER(B7),1))<91)
>
> When I put that same forumla into VBA, as validation criteria, it looks
> like:
>
> ..Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
> xlBetween, Formula1:="=AND(LEN(B7)=7,ISNUMBER(MID(B7,2,6)*1)
> ,CODE(LEFT(UPPER(B7),1))>64,CODE(LEFT(UPPER(B7),1))<91)"
>
> This line gives a Application defined or object defined error. I've run the
> first term (LEN(B7)=7 separately, and it runs fine. But the second term,
> ISNUMBER(MID(B7,2,6)*1)
> ,CODE(LEFT(UPPER(B7),1))>64,CODE(LEFT(UPPER(B7),1))<91)
>
> doesn't run. So does anyone see what is causing the problem?
> Thanks in advance!
>

On Fri, 7 Aug 2009 10:59:02 -0700, Luke M wrote:

> a few things:
>
> It probably just copied poorly into the newsgroup, but your formula is all
> on one line in VB?
>
> Is there any validation already in cell? I get the same error message when
> this occurs. You could add something like
>
> ActiveCell.Validaition.Delete
>
> to clear out any previous validation.

Thanks for your reply. Should have mentioned I already had the .delete in
there.

On Fri, 7 Aug 2009 11:01:01 -0700, Joel wrote:

> "AND(LEN(B7)=7,ISNUMBER(MID(B7,2,6)*1),CODE(LEFT(UPPER(B7),1))>64,CODE(LEFT(UPPER(B7),1))<91)"

Thanks for finding the error, that really helps. Now that the code runs, I
just have to figure out a formula that validates the data correctly.