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

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

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