Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application or object error - one last try
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(U PPER(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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application or object error - one last try
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(U PPER(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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application or object error - one last try
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(U PPER(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(U PPER(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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application or object error - one last try
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
runtime error '1004' application or object defined error | Excel Programming | |||
runtime error '1004' application or object defined error. Please help | Excel Programming | |||
Run Time Error 1004: Application or Object Defined Error | Excel Programming | |||
Run Time 1004 Error: Application or Object Difine Error | Excel Programming | |||
run-time error '1004': Application-defined or object-deifined error | Excel Programming |