On Tue, 18 Aug 2009 11:05:01 -0700, ker_01 wrote:

In quickly checking this code before, I already had a valid entry in the cell
- the code snippet below errors out when assigned to an empty cell because
the value doesn't match the validation.

Here is a workaround; put a valid value in the cell first, assign the
validation, then remove the cell contents. After that you should still be
able to do the subsequent steps of copy/paste as expected.

Sub SalgudTest2()

Sheet1.Range("A1").Value = "A123456"

Sheet1.Range("A1").Select
With Selection.Validation
.Delete
.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)"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

Sheet1.Range("A1").Value = ""
End Sub



"salgud" wrote:

On Fri, 14 Aug 2009 17:26:03 -0700, ker_01 wrote:
Thanks again for your reply.

Salgud-

Your response: "I don't want the validation formulae in 'target cells', I
have no problem doing that. What I want is the forumlae in the Custom field
in the Validation box". My prior suggestions, clarified here with code
samples, put your validation formula in the custom field in the data
validation box, not in the cells themselves. If I've misunderstood your
request, then I apologize and I'd need further clarification to offer any
additional assistance.

The following worked for me in Excel2003 to assign a *custom data validation
criteria* to cell A1

Sub SalgudTest1
Sheet1.Range("A1").Select
With Selection.Validation
.Delete
.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)"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

Put this in your VBA and run it to make sure that the code works for you,
and that the resulting validation works as expected. You'll be using cell A1
again in a moment, so run the code.

I entered the above code, substituting B7 for A1. When I tried to run the
code, I got an Object error, so I removed the = sign before the AND. That
ran, but won't validate a correct (A123456) ID. Any suggestions?

The problem I thought you were reporting in previous posts is that while
Excel accepts the above formula, it doesn't accept it once you try to make it
'dynamic', e.g. changing the target cell at runtime- for example, this
doesn't work:

TempCell = "A1"
Formula1:="=AND(LEN(" & TempCell & ")=7,ISNUMBER(MID(" & TempCell &
",2,6)*1),CODE(LEFT(UPPER(" & TempCell & "),1))64,CODE(LEFT(UPPER(" &
TempCell & "),1))<91)"

So my suggested workaround is to copy/paste a cell with the working data
validation rule- so that the data validation formula is automatically copied
as well. You can test this manually by copying cell A1 to A2, and then look
at the data validation formula for A2- it should be there and working just
fine. You can also do this via code:

Sub SalgudTest2
Sheet1.Range("A1").Select
Selection.Copy
Sheet1.Range("B1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End sub

If you need to dynamically change which cell is getting the validation
formula, change it by changing the copy/paste code, not the original data
validation formula. For example:

TempCell = "D4"
Sheet1.Range(TempCell).Select
ActiveSheet.Paste

Of course, if A1 had a value in it when you ran the copy/paste code, then
the value would have been brought over as well. Just use appropriate code to
delete any cell contents:

On Error Resume Next
Sheet1.Range("B1").Value =""
On Error Goto 0

Hope that helps,
Keith

"salgud" wrote:

On Fri, 14 Aug 2009 14:31:01 -0700, ker_01 wrote:

Salgud-

I responded to one of your previous posts; per my reply, I was able to get
your validation formula working by pasting it as a non-dynamic formula into
an unsed cell, then using VBA to copy/paste *that* cell to your target
(dynamic) range and letting Excel auto-modify the formula for the new target
range.

If you had problems with that approach, please post more information on
where you got stuck, and I'd be happy to try to help further.

Best,
Keith

"salgud" wrote:

I've been trying for 3 wks now to figure out a way to enter a validation
formula into the custom formula box that will both run, VBA wise, and
validate the data correctly. Basically, when I enter a name into cell, I
want it to check to see that the user entered an acceptable ID into another
cell (in the example, cell B7). The ID must be a letter followed by 6
numbers, e.g., A123456. I've made a couple of previous posts trying to get
this to work, but no one has come up with a working solution. I can't
understand why I'm having so much trouble.

I.e., the following forumla works great in a cell for testing the value:

=AND(LEN(B7)=7,NOT(ISNUMBER((LEFT(B7,1)*1))),ISNUM BER(RIGHT(B7,6)*1))

When I drop the = sign (why do some formulas require and = sign in the
custom validation box and others not?) and put it in the custom validation
formula box, it gives me a FALSE on a valid ID. Any ideas why? I've had the
same problem with other custom validation formulas, which makes me think
I'm missing something important in how they work and/or how to apply them.
I'm new to validation, though I've been doing VBA for a while. I'd
appreciate any help in getting this straigtened out.


Thanks for your reply.

I saw your previous post, but I don't want the validation formulae in
"target cells", I have no problem doing that. What I want is the forumlae
in the Custom field in the Validation box. Also, I don't understand how
entering the formula into GG1, then copying it to another cell, then
clearing the contents of both would get the formula into the Validation
Custom field. Maybe if you explained in more detail, I could follow it.



Sorry if I offended you in my previous post. That was not my intent. I only
needed some clarification on the procedure you recommended and wondered how
it worked.

Thanks for your help.