Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still not flying
I've submitted this before, but still can't get it to run. I'm entering
validation criteria into a spreadsheet, but keep getting an "Application or object not defined" error on the .Add Type line. The validation formula is looking for a 7 digit number, the first of which must be an alpha character, the other 6 must be numbers. Public Sub DataValidationClientID() 'Validate that Client ID field has a correct entry before allowing entry of Client Last Name With Selection.Validation .Delete .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=AND(CODE(UPPER(B" & lCurRow & "))64,CODE(UPPER(B" & _ lCurRow & "))<91,LEN(B" & lCurRow & ")=7,ISNUMBER(VALUE(RIGHT(B" & lCurRow & ",6)))" <----------- ERROR ' .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _ ' xlBetween, Formula1:="=AND(CODE(UPPER(B7))64,CODE(UPPER(B7)) <91,LEN(B7)=7,ISNUMBER(VALUE(RIGHT(B7,6))))" .IgnoreBlank = False .InCellDropdown = True .InputTitle = "" .ErrorTitle = "Incorrect ClientID" .InputMessage = "" .ErrorMessage = "There is no Client ID or an incorrect Client ID. " _ & "Please enter a correct Client ID in Column B before entering a Client Name" .ShowInput = False .ShowError = True End With Does anyone see the problem? Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still not flying
For starters, I don't see a value assigned to lCurRow before you try to use
it... HTH Keith "salgud" wrote: I've submitted this before, but still can't get it to run. I'm entering validation criteria into a spreadsheet, but keep getting an "Application or object not defined" error on the .Add Type line. The validation formula is looking for a 7 digit number, the first of which must be an alpha character, the other 6 must be numbers. Public Sub DataValidationClientID() 'Validate that Client ID field has a correct entry before allowing entry of Client Last Name With Selection.Validation .Delete .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=AND(CODE(UPPER(B" & lCurRow & "))64,CODE(UPPER(B" & _ lCurRow & "))<91,LEN(B" & lCurRow & ")=7,ISNUMBER(VALUE(RIGHT(B" & lCurRow & ",6)))" <----------- ERROR ' .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _ ' xlBetween, Formula1:="=AND(CODE(UPPER(B7))64,CODE(UPPER(B7)) <91,LEN(B7)=7,ISNUMBER(VALUE(RIGHT(B7,6))))" .IgnoreBlank = False .InCellDropdown = True .InputTitle = "" .ErrorTitle = "Incorrect ClientID" .InputMessage = "" .ErrorMessage = "There is no Client ID or an incorrect Client ID. " _ & "Please enter a correct Client ID in Column B before entering a Client Name" .ShowInput = False .ShowError = True End With Does anyone see the problem? Thanks in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still not flying
On Fri, 24 Jul 2009 11:33:27 -0600, salgud wrote:
I've submitted this before, but still can't get it to run. I'm entering validation criteria into a spreadsheet, but keep getting an "Application or object not defined" error on the .Add Type line. The validation formula is looking for a 7 digit number, the first of which must be an alpha character, the other 6 must be numbers. Public Sub DataValidationClientID() 'Validate that Client ID field has a correct entry before allowing entry of Client Last Name With Selection.Validation .Delete .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=AND(CODE(UPPER(B" & lCurRow & "))64,CODE(UPPER(B" & _ lCurRow & "))<91,LEN(B" & lCurRow & ")=7,ISNUMBER(VALUE(RIGHT(B" & lCurRow & ",6)))" <----------- ERROR ' .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _ ' xlBetween, Formula1:="=AND(CODE(UPPER(B7))64,CODE(UPPER(B7)) <91,LEN(B7)=7,ISNUMBER(VALUE(RIGHT(B7,6))))" .IgnoreBlank = False .InCellDropdown = True .InputTitle = "" .ErrorTitle = "Incorrect ClientID" .InputMessage = "" .ErrorMessage = "There is no Client ID or an incorrect Client ID. " _ & "Please enter a correct Client ID in Column B before entering a Client Name" .ShowInput = False .ShowError = True End With Does anyone see the problem? Thanks in advance. I should have mentioned, the variable lCurRow is declared and has the correct value when the program halts, and the correct cells for validation are selected. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still not flying
On Fri, 24 Jul 2009 10:48:01 -0700, ker_01 wrote:
For starters, I don't see a value assigned to lCurRow before you try to use it... HTH Keith "salgud" wrote: I've submitted this before, but still can't get it to run. I'm entering validation criteria into a spreadsheet, but keep getting an "Application or object not defined" error on the .Add Type line. The validation formula is looking for a 7 digit number, the first of which must be an alpha character, the other 6 must be numbers. Public Sub DataValidationClientID() 'Validate that Client ID field has a correct entry before allowing entry of Client Last Name With Selection.Validation .Delete .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=AND(CODE(UPPER(B" & lCurRow & "))64,CODE(UPPER(B" & _ lCurRow & "))<91,LEN(B" & lCurRow & ")=7,ISNUMBER(VALUE(RIGHT(B" & lCurRow & ",6)))" <----------- ERROR ' .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _ ' xlBetween, Formula1:="=AND(CODE(UPPER(B7))64,CODE(UPPER(B7)) <91,LEN(B7)=7,ISNUMBER(VALUE(RIGHT(B7,6))))" .IgnoreBlank = False .InCellDropdown = True .InputTitle = "" .ErrorTitle = "Incorrect ClientID" .InputMessage = "" .ErrorMessage = "There is no Client ID or an incorrect Client ID. " _ & "Please enter a correct Client ID in Column B before entering a Client Name" .ShowInput = False .ShowError = True End With Does anyone see the problem? Thanks in advance. See my follow up post below |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still not flying
I can't give you the technical explanation for why this works, but hopefully
this will help you keep moving forward, and one of the real gurus here can provide the "why". Although the formula can be entered directly, and a recorded macro also seems to continue to work, as soon as that formula string is broken out into a new line, or a concatenation of statements, it seems to fail for me too. I kind of suspect it has something to do with your use of the AND statement, and lumping all of this in Formula1 (vs Formula2) but that's the technical part I don't understand. There are probably even more eloquent ways to recreate your formula, I just used one that came to mind, and it seems to be working. Please give it a try, and if it doesn't work when tested against your real worksheet post back to the group. Range("A1").Select With Selection.Validation .Delete lCurRow = 6 StrTest = "=SUMPRODUCT((CODE(UPPER(B" & lCurRow & "))64)*1,(CODE(UPPER(B" & lCurRow & "))<91)*1,(LEN(B" & lCurRow & ")=7)*1,(ISNUMBER(VALUE(RIGHT(B" & lCurRow & ",6))))*1)" .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=StrTest End With HTH, Keith "salgud" wrote: On Fri, 24 Jul 2009 11:33:27 -0600, salgud wrote: I've submitted this before, but still can't get it to run. I'm entering validation criteria into a spreadsheet, but keep getting an "Application or object not defined" error on the .Add Type line. The validation formula is looking for a 7 digit number, the first of which must be an alpha character, the other 6 must be numbers. Public Sub DataValidationClientID() 'Validate that Client ID field has a correct entry before allowing entry of Client Last Name With Selection.Validation .Delete .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=AND(CODE(UPPER(B" & lCurRow & "))64,CODE(UPPER(B" & _ lCurRow & "))<91,LEN(B" & lCurRow & ")=7,ISNUMBER(VALUE(RIGHT(B" & lCurRow & ",6)))" <----------- ERROR ' .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _ ' xlBetween, Formula1:="=AND(CODE(UPPER(B7))64,CODE(UPPER(B7)) <91,LEN(B7)=7,ISNUMBER(VALUE(RIGHT(B7,6))))" .IgnoreBlank = False .InCellDropdown = True .InputTitle = "" .ErrorTitle = "Incorrect ClientID" .InputMessage = "" .ErrorMessage = "There is no Client ID or an incorrect Client ID. " _ & "Please enter a correct Client ID in Column B before entering a Client Name" .ShowInput = False .ShowError = True End With Does anyone see the problem? Thanks in advance. I should have mentioned, the variable lCurRow is declared and has the correct value when the program halts, and the correct cells for validation are selected. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still not flying
here is a function that you can use
Function ValidEntry(text As String) As Boolean 'input 7 characters chr 1 is A:Z 2-7 are numeric If Len(text) < 7 Then Exit Function Select Case Left(text, 1) Case "a" To "z", "A" To "Z" Case Else Exit Function End Select If IsNumeric(Mid(text, 2)) Then ValidEntry = True End If End Function first check is for 7 characters second check is the first character is a-z or A-Z last check is that the last 6 characters "isnumeric" which must be true only if all of the characters are numbers "salgud" wrote in message .. . On Fri, 24 Jul 2009 11:33:27 -0600, salgud wrote: I've submitted this before, but still can't get it to run. I'm entering validation criteria into a spreadsheet, but keep getting an "Application or object not defined" error on the .Add Type line. The validation formula is looking for a 7 digit number, the first of which must be an alpha character, the other 6 must be numbers. Public Sub DataValidationClientID() 'Validate that Client ID field has a correct entry before allowing entry of Client Last Name With Selection.Validation .Delete .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=AND(CODE(UPPER(B" & lCurRow & "))64,CODE(UPPER(B" & _ lCurRow & "))<91,LEN(B" & lCurRow & ")=7,ISNUMBER(VALUE(RIGHT(B" & lCurRow & ",6)))" <----------- ERROR ' .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _ ' xlBetween, Formula1:="=AND(CODE(UPPER(B7))64,CODE(UPPER(B7)) <91,LEN(B7)=7,ISNUMBER(VALUE(RIGHT(B7,6))))" .IgnoreBlank = False .InCellDropdown = True .InputTitle = "" .ErrorTitle = "Incorrect ClientID" .InputMessage = "" .ErrorMessage = "There is no Client ID or an incorrect Client ID. " _ & "Please enter a correct Client ID in Column B before entering a Client Name" .ShowInput = False .ShowError = True End With Does anyone see the problem? Thanks in advance. I should have mentioned, the variable lCurRow is declared and has the correct value when the program halts, and the correct cells for validation are selected. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still not flying
On Fri, 24 Jul 2009 22:11:59 +0100, Patrick Molloy wrote:
here is a function that you can use Function ValidEntry(text As String) As Boolean 'input 7 characters chr 1 is A:Z 2-7 are numeric If Len(text) < 7 Then Exit Function Select Case Left(text, 1) Case "a" To "z", "A" To "Z" Case Else Exit Function End Select If IsNumeric(Mid(text, 2)) Then ValidEntry = True End If End Function first check is for 7 characters second check is the first character is a-z or A-Z last check is that the last 6 characters "isnumeric" which must be true only if all of the characters are numbers "salgud" wrote in message .. . On Fri, 24 Jul 2009 11:33:27 -0600, salgud wrote: I've submitted this before, but still can't get it to run. I'm entering validation criteria into a spreadsheet, but keep getting an "Application or object not defined" error on the .Add Type line. The validation formula is looking for a 7 digit number, the first of which must be an alpha character, the other 6 must be numbers. Public Sub DataValidationClientID() 'Validate that Client ID field has a correct entry before allowing entry of Client Last Name With Selection.Validation .Delete .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=AND(CODE(UPPER(B" & lCurRow & "))64,CODE(UPPER(B" & _ lCurRow & "))<91,LEN(B" & lCurRow & ")=7,ISNUMBER(VALUE(RIGHT(B" & lCurRow & ",6)))" <----------- ERROR ' .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _ ' xlBetween, Formula1:="=AND(CODE(UPPER(B7))64,CODE(UPPER(B7)) <91,LEN(B7)=7,ISNUMBER(VALUE(RIGHT(B7,6))))" .IgnoreBlank = False .InCellDropdown = True .InputTitle = "" .ErrorTitle = "Incorrect ClientID" .InputMessage = "" .ErrorMessage = "There is no Client ID or an incorrect Client ID. " _ & "Please enter a correct Client ID in Column B before entering a Client Name" .ShowInput = False .ShowError = True End With Does anyone see the problem? Thanks in advance. I should have mentioned, the variable lCurRow is declared and has the correct value when the program halts, and the correct cells for validation are selected. Thanks for your reply. I know I can check it using code. In fact, the final phase of this program will be to run all these same checks on the spreadsheet in case they found a way around the validation (order of entry effects validation). But for the original data entry person, I want to use data validation so they know when the enter the data whether they're entry is correct or not. This seems far more difficult than I expected. Does anyone else have any ideas why this code doesn't work? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still not flying
On Fri, 24 Jul 2009 12:44:01 -0700, ker_01 wrote:
I can't give you the technical explanation for why this works, but hopefully this will help you keep moving forward, and one of the real gurus here can provide the "why". Although the formula can be entered directly, and a recorded macro also seems to continue to work, as soon as that formula string is broken out into a new line, or a concatenation of statements, it seems to fail for me too. I kind of suspect it has something to do with your use of the AND statement, and lumping all of this in Formula1 (vs Formula2) but that's the technical part I don't understand. There are probably even more eloquent ways to recreate your formula, I just used one that came to mind, and it seems to be working. Please give it a try, and if it doesn't work when tested against your real worksheet post back to the group. Range("A1").Select With Selection.Validation .Delete lCurRow = 6 StrTest = "=SUMPRODUCT((CODE(UPPER(B" & lCurRow & "))64)*1,(CODE(UPPER(B" & lCurRow & "))<91)*1,(LEN(B" & lCurRow & ")=7)*1,(ISNUMBER(VALUE(RIGHT(B" & lCurRow & ",6))))*1)" .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=StrTest End With HTH, Keith "salgud" wrote: On Fri, 24 Jul 2009 11:33:27 -0600, salgud wrote: I've submitted this before, but still can't get it to run. I'm entering validation criteria into a spreadsheet, but keep getting an "Application or object not defined" error on the .Add Type line. The validation formula is looking for a 7 digit number, the first of which must be an alpha character, the other 6 must be numbers. Public Sub DataValidationClientID() 'Validate that Client ID field has a correct entry before allowing entry of Client Last Name With Selection.Validation .Delete .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=AND(CODE(UPPER(B" & lCurRow & "))64,CODE(UPPER(B" & _ lCurRow & "))<91,LEN(B" & lCurRow & ")=7,ISNUMBER(VALUE(RIGHT(B" & lCurRow & ",6)))" <----------- ERROR ' .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _ ' xlBetween, Formula1:="=AND(CODE(UPPER(B7))64,CODE(UPPER(B7)) <91,LEN(B7)=7,ISNUMBER(VALUE(RIGHT(B7,6))))" .IgnoreBlank = False .InCellDropdown = True .InputTitle = "" .ErrorTitle = "Incorrect ClientID" .InputMessage = "" .ErrorMessage = "There is no Client ID or an incorrect Client ID. " _ & "Please enter a correct Client ID in Column B before entering a Client Name" .ShowInput = False .ShowError = True End With Does anyone see the problem? Thanks in advance. I should have mentioned, the variable lCurRow is declared and has the correct value when the program halts, and the correct cells for validation are selected. Thanks for your reply. Gave your code a try, same error. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still not flying
the custom validation formula is (for cell C6 )
=AND( LEN(C6)=7, ISNUMBER( MID(C6,2,6) *1 ) ) this checks (1) that the entry is 7 characters and (2) that the 2nd thro last are numeric -- if any if the last 6 are not numbers then the last 6 cannot be numeric "salgud" wrote in message . .. On Fri, 24 Jul 2009 22:11:59 +0100, Patrick Molloy wrote: here is a function that you can use Function ValidEntry(text As String) As Boolean 'input 7 characters chr 1 is A:Z 2-7 are numeric If Len(text) < 7 Then Exit Function Select Case Left(text, 1) Case "a" To "z", "A" To "Z" Case Else Exit Function End Select If IsNumeric(Mid(text, 2)) Then ValidEntry = True End If End Function first check is for 7 characters second check is the first character is a-z or A-Z last check is that the last 6 characters "isnumeric" which must be true only if all of the characters are numbers "salgud" wrote in message .. . On Fri, 24 Jul 2009 11:33:27 -0600, salgud wrote: I've submitted this before, but still can't get it to run. I'm entering validation criteria into a spreadsheet, but keep getting an "Application or object not defined" error on the .Add Type line. The validation formula is looking for a 7 digit number, the first of which must be an alpha character, the other 6 must be numbers. Public Sub DataValidationClientID() 'Validate that Client ID field has a correct entry before allowing entry of Client Last Name With Selection.Validation .Delete .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=AND(CODE(UPPER(B" & lCurRow & "))64,CODE(UPPER(B" & _ lCurRow & "))<91,LEN(B" & lCurRow & ")=7,ISNUMBER(VALUE(RIGHT(B" & lCurRow & ",6)))" <----------- ERROR ' .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _ ' xlBetween, Formula1:="=AND(CODE(UPPER(B7))64,CODE(UPPER(B7)) <91,LEN(B7)=7,ISNUMBER(VALUE(RIGHT(B7,6))))" .IgnoreBlank = False .InCellDropdown = True .InputTitle = "" .ErrorTitle = "Incorrect ClientID" .InputMessage = "" .ErrorMessage = "There is no Client ID or an incorrect Client ID. " _ & "Please enter a correct Client ID in Column B before entering a Client Name" .ShowInput = False .ShowError = True End With Does anyone see the problem? Thanks in advance. I should have mentioned, the variable lCurRow is declared and has the correct value when the program halts, and the correct cells for validation are selected. Thanks for your reply. I know I can check it using code. In fact, the final phase of this program will be to run all these same checks on the spreadsheet in case they found a way around the validation (order of entry effects validation). But for the original data entry person, I want to use data validation so they know when the enter the data whether they're entry is correct or not. This seems far more difficult than I expected. Does anyone else have any ideas why this code doesn't work? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still not flying
On Mon, 27 Jul 2009 17:17:39 +0100, Patrick Molloy wrote:
the custom validation formula is (for cell C6 ) =AND( LEN(C6)=7, ISNUMBER( MID(C6,2,6) *1 ) ) this checks (1) that the entry is 7 characters and (2) that the 2nd thro last are numeric -- if any if the last 6 are not numbers then the last 6 cannot be numeric "salgud" wrote in message . .. On Fri, 24 Jul 2009 22:11:59 +0100, Patrick Molloy wrote: here is a function that you can use Function ValidEntry(text As String) As Boolean 'input 7 characters chr 1 is A:Z 2-7 are numeric If Len(text) < 7 Then Exit Function Select Case Left(text, 1) Case "a" To "z", "A" To "Z" Case Else Exit Function End Select If IsNumeric(Mid(text, 2)) Then ValidEntry = True End If End Function first check is for 7 characters second check is the first character is a-z or A-Z last check is that the last 6 characters "isnumeric" which must be true only if all of the characters are numbers "salgud" wrote in message .. . On Fri, 24 Jul 2009 11:33:27 -0600, salgud wrote: I've submitted this before, but still can't get it to run. I'm entering validation criteria into a spreadsheet, but keep getting an "Application or object not defined" error on the .Add Type line. The validation formula is looking for a 7 digit number, the first of which must be an alpha character, the other 6 must be numbers. Public Sub DataValidationClientID() 'Validate that Client ID field has a correct entry before allowing entry of Client Last Name With Selection.Validation .Delete .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=AND(CODE(UPPER(B" & lCurRow & "))64,CODE(UPPER(B" & _ lCurRow & "))<91,LEN(B" & lCurRow & ")=7,ISNUMBER(VALUE(RIGHT(B" & lCurRow & ",6)))" <----------- ERROR ' .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _ ' xlBetween, Formula1:="=AND(CODE(UPPER(B7))64,CODE(UPPER(B7)) <91,LEN(B7)=7,ISNUMBER(VALUE(RIGHT(B7,6))))" .IgnoreBlank = False .InCellDropdown = True .InputTitle = "" .ErrorTitle = "Incorrect ClientID" .InputMessage = "" .ErrorMessage = "There is no Client ID or an incorrect Client ID. " _ & "Please enter a correct Client ID in Column B before entering a Client Name" .ShowInput = False .ShowError = True End With Does anyone see the problem? Thanks in advance. I should have mentioned, the variable lCurRow is declared and has the correct value when the program halts, and the correct cells for validation are selected. Thanks for your reply. I know I can check it using code. In fact, the final phase of this program will be to run all these same checks on the spreadsheet in case they found a way around the validation (order of entry effects validation). But for the original data entry person, I want to use data validation so they know when the enter the data whether they're entry is correct or not. This seems far more difficult than I expected. Does anyone else have any ideas why this code doesn't work? Thanks for your reply. That formula doesn't work, not sure why. But I don't understand why you're multiplying by 1. Either way, when I test it, I get true when the first character is a number (7 numbers, no first letter). |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still not flying
Patrick's sample appears to cover two of your three criteria. I just tested
this, and it works on Excel2003 on my PC (on cell A1) and incorporates your need for a first-digit alpha between a-z or A-Z. .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=AND(LEN(A1)=7,ISNUMBER(MID(A1,2,6)*1), CODE(LEFT(UPPER(A1),1))64,CODE(LEFT(UPPER(A1),1)) <91)" However, I'm still not clear that this will solve your original request- your original validation formula was good, the problem was getting VBA to assign a text string or multi-part string (using lCurrRow) to a validation formula. Let me propose a workaround; use the formula above to create the validation in an area of the worksheet you aren't using- maybe cell GG1. Then use copy/paste to copy the validation formula to your target cells. When you are done you can always delete GG1, if you feel that is necessary. Copy the validation without retaining any existing entered value using something like: Range("GG1").Select Selection.Copy Range("F" & lCurrRow).Select ActiveSheet.Paste Application.CutCopyMode = False Selection.ClearContents 'to get rid of any values that were in the source cell (note: this is longhand- you can collapse this into fewer lines of code if you want) You asked about the *1; it looks like the multiplication by 1 is a shorthand way to evaluate a string number back into a "real" number without having to use the VALUE function. New to me, I'll have to remember that. I still don't know why your original attempt didn't work, but hopefully the above workaround will get you back on track HTH, Keith "salgud" wrote: On Mon, 27 Jul 2009 17:17:39 +0100, Patrick Molloy wrote: the custom validation formula is (for cell C6 ) =AND( LEN(C6)=7, ISNUMBER( MID(C6,2,6) *1 ) ) this checks (1) that the entry is 7 characters and (2) that the 2nd thro last are numeric -- if any if the last 6 are not numbers then the last 6 cannot be numeric "salgud" wrote in message . .. On Fri, 24 Jul 2009 22:11:59 +0100, Patrick Molloy wrote: here is a function that you can use Function ValidEntry(text As String) As Boolean 'input 7 characters chr 1 is A:Z 2-7 are numeric If Len(text) < 7 Then Exit Function Select Case Left(text, 1) Case "a" To "z", "A" To "Z" Case Else Exit Function End Select If IsNumeric(Mid(text, 2)) Then ValidEntry = True End If End Function first check is for 7 characters second check is the first character is a-z or A-Z last check is that the last 6 characters "isnumeric" which must be true only if all of the characters are numbers "salgud" wrote in message .. . On Fri, 24 Jul 2009 11:33:27 -0600, salgud wrote: I've submitted this before, but still can't get it to run. I'm entering validation criteria into a spreadsheet, but keep getting an "Application or object not defined" error on the .Add Type line. The validation formula is looking for a 7 digit number, the first of which must be an alpha character, the other 6 must be numbers. Public Sub DataValidationClientID() 'Validate that Client ID field has a correct entry before allowing entry of Client Last Name With Selection.Validation .Delete .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=AND(CODE(UPPER(B" & lCurRow & "))64,CODE(UPPER(B" & _ lCurRow & "))<91,LEN(B" & lCurRow & ")=7,ISNUMBER(VALUE(RIGHT(B" & lCurRow & ",6)))" <----------- ERROR ' .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _ ' xlBetween, Formula1:="=AND(CODE(UPPER(B7))64,CODE(UPPER(B7)) <91,LEN(B7)=7,ISNUMBER(VALUE(RIGHT(B7,6))))" .IgnoreBlank = False .InCellDropdown = True .InputTitle = "" .ErrorTitle = "Incorrect ClientID" .InputMessage = "" .ErrorMessage = "There is no Client ID or an incorrect Client ID. " _ & "Please enter a correct Client ID in Column B before entering a Client Name" .ShowInput = False .ShowError = True End With Does anyone see the problem? Thanks in advance. I should have mentioned, the variable lCurRow is declared and has the correct value when the program halts, and the correct cells for validation are selected. Thanks for your reply. I know I can check it using code. In fact, the final phase of this program will be to run all these same checks on the spreadsheet in case they found a way around the validation (order of entry effects validation). But for the original data entry person, I want to use data validation so they know when the enter the data whether they're entry is correct or not. This seems far more difficult than I expected. Does anyone else have any ideas why this code doesn't work? Thanks for your reply. That formula doesn't work, not sure why. But I don't understand why you're multiplying by 1. Either way, when I test it, I get true when the first character is a number (7 numbers, no first letter). |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still not flying
it works in 2003, otherwise I wouldn't have posted it.
the x1 coerces the text to a number see my demo book: http://cid-b8e56c9a5f311cb7.skydrive...validation.xls "salgud" wrote in message ... On Mon, 27 Jul 2009 17:17:39 +0100, Patrick Molloy wrote: the custom validation formula is (for cell C6 ) =AND( LEN(C6)=7, ISNUMBER( MID(C6,2,6) *1 ) ) this checks (1) that the entry is 7 characters and (2) that the 2nd thro last are numeric -- if any if the last 6 are not numbers then the last 6 cannot be numeric "salgud" wrote in message . .. On Fri, 24 Jul 2009 22:11:59 +0100, Patrick Molloy wrote: here is a function that you can use Function ValidEntry(text As String) As Boolean 'input 7 characters chr 1 is A:Z 2-7 are numeric If Len(text) < 7 Then Exit Function Select Case Left(text, 1) Case "a" To "z", "A" To "Z" Case Else Exit Function End Select If IsNumeric(Mid(text, 2)) Then ValidEntry = True End If End Function first check is for 7 characters second check is the first character is a-z or A-Z last check is that the last 6 characters "isnumeric" which must be true only if all of the characters are numbers "salgud" wrote in message .. . On Fri, 24 Jul 2009 11:33:27 -0600, salgud wrote: I've submitted this before, but still can't get it to run. I'm entering validation criteria into a spreadsheet, but keep getting an "Application or object not defined" error on the .Add Type line. The validation formula is looking for a 7 digit number, the first of which must be an alpha character, the other 6 must be numbers. Public Sub DataValidationClientID() 'Validate that Client ID field has a correct entry before allowing entry of Client Last Name With Selection.Validation .Delete .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=AND(CODE(UPPER(B" & lCurRow & "))64,CODE(UPPER(B" & _ lCurRow & "))<91,LEN(B" & lCurRow & ")=7,ISNUMBER(VALUE(RIGHT(B" & lCurRow & ",6)))" <----------- ERROR ' .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _ ' xlBetween, Formula1:="=AND(CODE(UPPER(B7))64,CODE(UPPER(B7)) <91,LEN(B7)=7,ISNUMBER(VALUE(RIGHT(B7,6))))" .IgnoreBlank = False .InCellDropdown = True .InputTitle = "" .ErrorTitle = "Incorrect ClientID" .InputMessage = "" .ErrorMessage = "There is no Client ID or an incorrect Client ID. " _ & "Please enter a correct Client ID in Column B before entering a Client Name" .ShowInput = False .ShowError = True End With Does anyone see the problem? Thanks in advance. I should have mentioned, the variable lCurRow is declared and has the correct value when the program halts, and the correct cells for validation are selected. Thanks for your reply. I know I can check it using code. In fact, the final phase of this program will be to run all these same checks on the spreadsheet in case they found a way around the validation (order of entry effects validation). But for the original data entry person, I want to use data validation so they know when the enter the data whether they're entry is correct or not. This seems far more difficult than I expected. Does anyone else have any ideas why this code doesn't work? Thanks for your reply. That formula doesn't work, not sure why. But I don't understand why you're multiplying by 1. Either way, when I test it, I get true when the first character is a number (7 numbers, no first letter). |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still not flying
"Patrick Molloy" wrote: it works in 2003, otherwise I wouldn't have posted it. the x1 coerces the text to a number I definitely wasn't doubting you, nor did I intend to convey any disrespect- I've been using XL (still using 2003 whenever possible) for a long time, and it is little gems like this that remind me that my code is still plenty inefficient ;-) I continually appreciate your postings, along with the other MVPs and kind experts who contribute to the group. Best, Keith |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still not flying
On Mon, 27 Jul 2009 22:38:36 +0100, Patrick Molloy wrote:
it works in 2003, otherwise I wouldn't have posted it. the x1 coerces the text to a number see my demo book: http://cid-b8e56c9a5f311cb7.skydrive...validation.xls "salgud" wrote in message ... On Mon, 27 Jul 2009 17:17:39 +0100, Patrick Molloy wrote: the custom validation formula is (for cell C6 ) =AND( LEN(C6)=7, ISNUMBER( MID(C6,2,6) *1 ) ) this checks (1) that the entry is 7 characters and (2) that the 2nd thro last are numeric -- if any if the last 6 are not numbers then the last 6 cannot be numeric "salgud" wrote in message . .. On Fri, 24 Jul 2009 22:11:59 +0100, Patrick Molloy wrote: here is a function that you can use Function ValidEntry(text As String) As Boolean 'input 7 characters chr 1 is A:Z 2-7 are numeric If Len(text) < 7 Then Exit Function Select Case Left(text, 1) Case "a" To "z", "A" To "Z" Case Else Exit Function End Select If IsNumeric(Mid(text, 2)) Then ValidEntry = True End If End Function first check is for 7 characters second check is the first character is a-z or A-Z last check is that the last 6 characters "isnumeric" which must be true only if all of the characters are numbers "salgud" wrote in message .. . On Fri, 24 Jul 2009 11:33:27 -0600, salgud wrote: I've submitted this before, but still can't get it to run. I'm entering validation criteria into a spreadsheet, but keep getting an "Application or object not defined" error on the .Add Type line. The validation formula is looking for a 7 digit number, the first of which must be an alpha character, the other 6 must be numbers. Public Sub DataValidationClientID() 'Validate that Client ID field has a correct entry before allowing entry of Client Last Name With Selection.Validation .Delete .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=AND(CODE(UPPER(B" & lCurRow & "))64,CODE(UPPER(B" & _ lCurRow & "))<91,LEN(B" & lCurRow & ")=7,ISNUMBER(VALUE(RIGHT(B" & lCurRow & ",6)))" <----------- ERROR ' .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _ ' xlBetween, Formula1:="=AND(CODE(UPPER(B7))64,CODE(UPPER(B7)) <91,LEN(B7)=7,ISNUMBER(VALUE(RIGHT(B7,6))))" .IgnoreBlank = False .InCellDropdown = True .InputTitle = "" .ErrorTitle = "Incorrect ClientID" .InputMessage = "" .ErrorMessage = "There is no Client ID or an incorrect Client ID. " _ & "Please enter a correct Client ID in Column B before entering a Client Name" .ShowInput = False .ShowError = True End With Does anyone see the problem? Thanks in advance. I should have mentioned, the variable lCurRow is declared and has the correct value when the program halts, and the correct cells for validation are selected. Thanks for your reply. I know I can check it using code. In fact, the final phase of this program will be to run all these same checks on the spreadsheet in case they found a way around the validation (order of entry effects validation). But for the original data entry person, I want to use data validation so they know when the enter the data whether they're entry is correct or not. This seems far more difficult than I expected. Does anyone else have any ideas why this code doesn't work? Thanks for your reply. That formula doesn't work, not sure why. But I don't understand why you're multiplying by 1. Either way, when I test it, I get true when the first character is a number (7 numbers, no first letter). Thanks for your reply, Patrick. I too amd using XL2003, which helps. Acutally, the second spreadsheet demos that that forumula doesn't work. Note that the first example, A123456, does meet the criteria I'm looking for (one letter, six numbers) and your validation formula yields "FALSE". That one needs to be TRUE. I thought maybe the multiplying by 1 did something like that. Clever. In any case, I'm giving up on validating the ID number. I've gotten several formulae that do work in the spreadsheet, but none of them translate into VBA for creating data validation criteria. I've spend the better part of several days trying to get this to work, with no sucess. I had always planned to have a separate macro to check all these validated data and to mark any that don't meet the criteria. With this one, I'll just do a VBA check on the ID field and if the ID fails, send the spreadsheet back to the user to correct. Not as efficient as I wanted to be, but more efficient than spending more fruitless days trying to get data validation to work on this. I guess there are limits to what can be done by any method, and I've hit the limits for this one. I need to move ahead and get some work done. Nonetheless, I do appreciate everyone's efforts at helping me make this work. Thank you all for your suggestions! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Flying Bricks Chart | Charts and Charting in Excel | |||
Flying Hours | Excel Worksheet Functions | |||
Anyone have a template for a flying club? | Charts and Charting in Excel | |||
Calculating Flying hours allowing for GMT or BST | Excel Worksheet Functions |