View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
steve steve is offline
external usenet poster
 
Posts: 576
Default 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