| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data I have Excel 2002 as part of Office XP Standard. I tried alidation, I think
I need to ensure that users only enter a customer number in the correct
format in a cell. The format is an 'A' followed by 7 numbers. How can I use validation to ensure only this type of entry is allowed? Gareth |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Data I have Excel 2002 as part of Office XP Standard. I tried alidation, I think
Gareth,
For Data Validation (either manual or by code). Set "Allow:" to "Custom" In the "Formula:" box put =AND(LEN(A1)=8,LOWER(LEFT(A1,1))="a") (this is for cell A1, adjust as needed) change to =AND(LEN(A1)=8,A1="A") to accept only capital A In code it would look something like this With Selection.Validation .Delete .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, _ Operator:= _ xlBetween, Formula1:="=AND(LEN(E1)=8,E1:E1=""A"")" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With [you can remove or amend most of the lines after the formula part] hth -- sb "Gareth" wrote in message ... I need to ensure that users only enter a customer number in the correct format in a cell. The format is an 'A' followed by 7 numbers. How can I use validation to ensure only this type of entry is allowed? Gareth |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Data I have Excel 2002 as part of Office XP Standard. I tried alidation, I think
Steve
Cannot seem to get it to work, I can see what LEN does and what LEFT(A1,1) does but it doesn't work. Also, is it possible to ensure that the last 7 characters are numerical? Gareth "steve" wrote in message ... Gareth, For Data Validation (either manual or by code). Set "Allow:" to "Custom" In the "Formula:" box put =AND(LEN(A1)=8,LOWER(LEFT(A1,1))="a") (this is for cell A1, adjust as needed) change to =AND(LEN(A1)=8,A1="A") to accept only capital A In code it would look something like this With Selection.Validation .Delete .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, _ Operator:= _ xlBetween, Formula1:="=AND(LEN(E1)=8,E1:E1=""A"")" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With [you can remove or amend most of the lines after the formula part] hth -- sb "Gareth" wrote in message ... I need to ensure that users only enter a customer number in the correct format in a cell. The format is an 'A' followed by 7 numbers. How can I use validation to ensure only this type of entry is allowed? Gareth |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Data I have Excel 2002 as part of Office XP Standard. I tried alidation, I think
Tom,
Thanks for pointing me to "ISNUMBER". I was getting ready to suggest an event macro. A further mod would be to use "VALUE" instead of "*1". =AND(LEN(A1)=8,LOWER(LEFT(A1,1))="a",ISNUMBER(VALU E(RIGHT(A1,7))) [watch word wrap] -- sb "Tom Ogilvy" wrote in message ... Steve's suggestion worked for me. did you select A1 and then do Data=Validation, select custom and paste in the formula? Here is a modification to restrict the last 7 to numbers: =AND(LEN(A1)=8,LOWER(LEFT(A1,1))="a",ISNUMBER(RIGH T(A1,7)*1)) -- Regards, Tom Ogilvy "Gareth" wrote in message ... Steve Cannot seem to get it to work, I can see what LEN does and what LEFT(A1,1) does but it doesn't work. Also, is it possible to ensure that the last 7 characters are numerical? Gareth "steve" wrote in message ... Gareth, For Data Validation (either manual or by code). Set "Allow:" to "Custom" In the "Formula:" box put =AND(LEN(A1)=8,LOWER(LEFT(A1,1))="a") (this is for cell A1, adjust as needed) change to =AND(LEN(A1)=8,A1="A") to accept only capital A In code it would look something like this With Selection.Validation .Delete .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, _ Operator:= _ xlBetween, Formula1:="=AND(LEN(E1)=8,E1:E1=""A"")" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With [you can remove or amend most of the lines after the formula part] hth -- sb "Gareth" wrote in message ... I need to ensure that users only enter a customer number in the correct format in a cell. The format is an 'A' followed by 7 numbers. How can I use validation to ensure only this type of entry is allowed? Gareth |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Data I have Excel 2002 as part of Office XP Standard. I tried alidation, I think
Tom and steve
it still allows me to enter a small 'a' Gareth "steve" wrote in message ... Tom, Thanks for pointing me to "ISNUMBER". I was getting ready to suggest an event macro. A further mod would be to use "VALUE" instead of "*1". =AND(LEN(A1)=8,LOWER(LEFT(A1,1))="a",ISNUMBER(VALU E(RIGHT(A1,7))) [watch word wrap] -- sb "Tom Ogilvy" wrote in message ... Steve's suggestion worked for me. did you select A1 and then do Data=Validation, select custom and paste in the formula? Here is a modification to restrict the last 7 to numbers: =AND(LEN(A1)=8,LOWER(LEFT(A1,1))="a",ISNUMBER(RIGH T(A1,7)*1)) -- Regards, Tom Ogilvy "Gareth" wrote in message ... Steve Cannot seem to get it to work, I can see what LEN does and what LEFT(A1,1) does but it doesn't work. Also, is it possible to ensure that the last 7 characters are numerical? Gareth "steve" wrote in message ... Gareth, For Data Validation (either manual or by code). Set "Allow:" to "Custom" In the "Formula:" box put =AND(LEN(A1)=8,LOWER(LEFT(A1,1))="a") (this is for cell A1, adjust as needed) change to =AND(LEN(A1)=8,A1="A") to accept only capital A In code it would look something like this With Selection.Validation .Delete .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, _ Operator:= _ xlBetween, Formula1:="=AND(LEN(E1)=8,E1:E1=""A"")" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With [you can remove or amend most of the lines after the formula part] hth -- sb "Gareth" wrote in message ... I need to ensure that users only enter a customer number in the correct format in a cell. The format is an 'A' followed by 7 numbers. How can I use validation to ensure only this type of entry is allowed? Gareth |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Data I have Excel 2002 as part of Office XP Standard. I tried alidation, I think
Gareth,
To restrict it to an upper case "A" use: =AND(LEN(A1)=8,LEFT(A1,1)="A",ISNUMBER(VALUE(RIGHT (A1,7))) The previous formula allowed either upper or lower case. -- sb "Gareth" wrote in message ... Tom and steve it still allows me to enter a small 'a' Gareth "steve" wrote in message ... Tom, Thanks for pointing me to "ISNUMBER". I was getting ready to suggest an event macro. A further mod would be to use "VALUE" instead of "*1". =AND(LEN(A1)=8,LOWER(LEFT(A1,1))="a",ISNUMBER(VALU E(RIGHT(A1,7))) [watch word wrap] -- sb "Tom Ogilvy" wrote in message ... Steve's suggestion worked for me. did you select A1 and then do Data=Validation, select custom and paste in the formula? Here is a modification to restrict the last 7 to numbers: =AND(LEN(A1)=8,LOWER(LEFT(A1,1))="a",ISNUMBER(RIGH T(A1,7)*1)) -- Regards, Tom Ogilvy "Gareth" wrote in message ... Steve Cannot seem to get it to work, I can see what LEN does and what LEFT(A1,1) does but it doesn't work. Also, is it possible to ensure that the last 7 characters are numerical? Gareth "steve" wrote in message ... Gareth, For Data Validation (either manual or by code). Set "Allow:" to "Custom" In the "Formula:" box put =AND(LEN(A1)=8,LOWER(LEFT(A1,1))="a") (this is for cell A1, adjust as needed) change to =AND(LEN(A1)=8,A1="A") to accept only capital A In code it would look something like this With Selection.Validation .Delete .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, _ Operator:= _ xlBetween, Formula1:="=AND(LEN(E1)=8,E1:E1=""A"")" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With [you can remove or amend most of the lines after the formula part] hth -- sb "Gareth" wrote in message ... I need to ensure that users only enter a customer number in the correct format in a cell. The format is an 'A' followed by 7 numbers. How can I use validation to ensure only this type of entry is allowed? Gareth |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Data I have Excel 2002 as part of Office XP Standard. I tried alidation, I think
Gareth,
This will ensure a capital A =AND(LEN(A1)=8,CODE(A1)=65,ISNUMBER(VALUE(RIGHT(A1 ,7)))) [watch for word wrap] Sorry for my error! I thought that LEFT(A1,1)="A" was case sensitive. Now I know better... -- sb "Gareth" wrote in message ... still allows a small 'a'! "steve" wrote in message ... Gareth, To restrict it to an upper case "A" use: =AND(LEN(A1)=8,LEFT(A1,1)="A",ISNUMBER(VALUE(RIGHT (A1,7))) The previous formula allowed either upper or lower case. -- sb "Gareth" wrote in message ... Tom and steve it still allows me to enter a small 'a' Gareth "steve" wrote in message ... Tom, Thanks for pointing me to "ISNUMBER". I was getting ready to suggest an event macro. A further mod would be to use "VALUE" instead of "*1". =AND(LEN(A1)=8,LOWER(LEFT(A1,1))="a",ISNUMBER(VALU E(RIGHT(A1,7))) [watch word wrap] -- sb "Tom Ogilvy" wrote in message ... Steve's suggestion worked for me. did you select A1 and then do Data=Validation, select custom and paste in the formula? Here is a modification to restrict the last 7 to numbers: =AND(LEN(A1)=8,LOWER(LEFT(A1,1))="a",ISNUMBER(RIGH T(A1,7)*1)) -- Regards, Tom Ogilvy "Gareth" wrote in message ... Steve Cannot seem to get it to work, I can see what LEN does and what LEFT(A1,1) does but it doesn't work. Also, is it possible to ensure that the last 7 characters are numerical? Gareth "steve" wrote in message ... Gareth, For Data Validation (either manual or by code). Set "Allow:" to "Custom" In the "Formula:" box put =AND(LEN(A1)=8,LOWER(LEFT(A1,1))="a") (this is for cell A1, adjust as needed) change to =AND(LEN(A1)=8,A1="A") to accept only capital A In code it would look something like this With Selection.Validation .Delete .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, _ Operator:= _ xlBetween, Formula1:="=AND(LEN(E1)=8,E1:E1=""A"")" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With [you can remove or amend most of the lines after the formula part] hth -- sb "Gareth" wrote in message ... I need to ensure that users only enter a customer number in the correct format in a cell. The format is an 'A' followed by 7 numbers. How can I use validation to ensure only this type of entry is allowed? Gareth |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Data I have Excel 2002 as part of Office XP Standard. I tried alidation, I think
steve
many, many thanks for your help over the last couple of days. Gareth "steve" wrote in message ... Gareth, This will ensure a capital A =AND(LEN(A1)=8,CODE(A1)=65,ISNUMBER(VALUE(RIGHT(A1 ,7)))) [watch for word wrap] Sorry for my error! I thought that LEFT(A1,1)="A" was case sensitive. Now I know better... -- sb "Gareth" wrote in message ... still allows a small 'a'! "steve" wrote in message ... Gareth, To restrict it to an upper case "A" use: =AND(LEN(A1)=8,LEFT(A1,1)="A",ISNUMBER(VALUE(RIGHT (A1,7))) The previous formula allowed either upper or lower case. -- sb "Gareth" wrote in message ... Tom and steve it still allows me to enter a small 'a' Gareth "steve" wrote in message ... Tom, Thanks for pointing me to "ISNUMBER". I was getting ready to suggest an event macro. A further mod would be to use "VALUE" instead of "*1". =AND(LEN(A1)=8,LOWER(LEFT(A1,1))="a",ISNUMBER(VALU E(RIGHT(A1,7))) [watch word wrap] -- sb "Tom Ogilvy" wrote in message ... Steve's suggestion worked for me. did you select A1 and then do Data=Validation, select custom and paste in the formula? Here is a modification to restrict the last 7 to numbers: =AND(LEN(A1)=8,LOWER(LEFT(A1,1))="a",ISNUMBER(RIGH T(A1,7)*1)) -- Regards, Tom Ogilvy "Gareth" wrote in message ... Steve Cannot seem to get it to work, I can see what LEN does and what LEFT(A1,1) does but it doesn't work. Also, is it possible to ensure that the last 7 characters are numerical? Gareth "steve" wrote in message ... Gareth, For Data Validation (either manual or by code). Set "Allow:" to "Custom" In the "Formula:" box put =AND(LEN(A1)=8,LOWER(LEFT(A1,1))="a") (this is for cell A1, adjust as needed) change to =AND(LEN(A1)=8,A1="A") to accept only capital A In code it would look something like this With Selection.Validation .Delete .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, _ Operator:= _ xlBetween, Formula1:="=AND(LEN(E1)=8,E1:E1=""A"")" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With [you can remove or amend most of the lines after the formula part] hth -- sb "Gareth" wrote in message ... I need to ensure that users only enter a customer number in the correct format in a cell. The format is an 'A' followed by 7 numbers. How can I use validation to ensure only this type of entry is allowed? Gareth |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Install Office Standard XP on Vista. Uninstall Office 2007 Trial? | Excel Discussion (Misc queries) | |||
| Excel Office Standard Editiion 2003 convert | Excel Discussion (Misc queries) | |||
| Why no XML in Excel 2003 with Office Standard? | Excel Discussion (Misc queries) | |||
| Standard Balance Sheet format in Microsoft Office Excel 2003(2) | Excel Worksheet Functions | |||
| can't find map button in Excel in Office Standard Edition 2003 | Excel Worksheet Functions | |||
![Gareth[_3_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
