Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still can't validate entry
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still can't validate entry
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" 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still can't validate entry
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still can't validate entry
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still can't validate entry
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. 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. 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still can't validate entry
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validate Entry | Excel Programming | |||
MsgBox to validate an entry | Excel Programming | |||
Validate textbox entry | Excel Programming | |||
Validate Combobox entry | Excel Programming | |||
Validate Textbox entry | Excel Programming |