Still can't validate entry

[salgud]

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))),ISNUMBER(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.

 

[ker_01]

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]

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

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.

 

[ker_01]

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.

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]

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?

 

[ker_01]

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]

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.

I'm not clear what you're saying here. The validation is in cell D7 (in the
original spreadsheet). The data to be validated, the ID, is in cell B7. Are
you saying there's a problem because B7 is blank when the validation
formula is entered into the Custom validation formula field? If that were
true, it would be very difficult to use validation anywhere because
usually, when the validation is "installed" by the user, the data to be
validated isn't there yet. A blank cell would be the norm. So data
validation must be designed to accept a blank cell, or for that matter,
anything in a cell, until it is triggered. (This in addition to the option
to "allow blank cells") In this case, it's triggered when someone tries to
enter a value in D7. Then, and only then, I would suspect, is the
validation criteria invoked to see if the contents of B7 are valid or not.
Otherwise, validation wouldn't work in most cases. So I don't think that
what's in the validated cell (in this instance, cell B7), would have any
effect being able to enter a custom validation formula in cell D7. Am I
wrong?

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.

I'm not clear how copying and pasting (either manually or by macro) the
formula from one cell (in your example, GG1) to another cell is going to
help to get a working formula into the custom validation field. You state
in your post you don't understand why it works either. I thought I'd try
your method just to see what happens, but I don't know if I copy the
formula from GG1 to some other cell, using a macro, before or after I enter
the formula into the Custom Validation field. And why would it, done before
or after, have any effect on the formula in the Custom Validation field
anyway? And why would copying and pasting it using a macro work when
copying and pasting this formula manually won't work?

Also, do I copy and paste it from GG1 to B7 (the cell whose value is being
validated) or to D7, the cell where the custom validation formula resides?

What I'm hearing is that somehow, having the validation formula in one of
these cells will make the same formula work in the Custom Validation field,
and you can't explain why. I can't comprehend how entering the formula into
either of these fields is going to make the one entered in the Custom
Validation field function correctly when it doesn't otherwise. Can you give
me some explanation as to why this would work?

At the least, can you give me more detailed instructions as to:
1. When to run the macro to copy the formula from GG1 to the other cell?
Before or after I enter the formula into the Custom Validation field?
2. Which cell to copy the formula from GG1 into? B7, the cell whose value
is being validated, or D7, the cell where the validation resides?

Give me this information, and I'll try your method, though I have to say,
it feels more like voodoo than normal XL practice to me!

In any case, thanks for your help and your patience.

 

[salgud]

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

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.