Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spaces and Data Validation
I put VBE code in for my data validation, example is below (it uses range
names). However, if the user enters a response manually (without using the drop down menu), the code doesn't reject extra spaces entered. So when I really need "Yes" for example, it takes "Yes " or " Yes". Am I doing something wrong in my code? If so, how can I fix this so that the user cannot add extra spaces (by adding the Application.Trim somewhere)? I have a large workbook with multiple areas affected, that is almost in its last stages of finalization (hopefully), so am praying this is a relatively easy thing to fix. Thanks for any help....Paige Example is: With Range("PM1_1,PM3_1,PM4_1,PM5_1,PM6_1").Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="Yes,No" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "Invalid entry; please use the drop-down menu to select a response." .ShowInput = True .ShowError = True End With |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spaces and Data Validation
Try:
Formula1:="""Yes"",""No""" -- Vasant "Paige" wrote in message ... I put VBE code in for my data validation, example is below (it uses range names). However, if the user enters a response manually (without using the drop down menu), the code doesn't reject extra spaces entered. So when I really need "Yes" for example, it takes "Yes " or " Yes". Am I doing something wrong in my code? If so, how can I fix this so that the user cannot add extra spaces (by adding the Application.Trim somewhere)? I have a large workbook with multiple areas affected, that is almost in its last stages of finalization (hopefully), so am praying this is a relatively easy thing to fix. Thanks for any help....Paige Example is: With Range("PM1_1,PM3_1,PM4_1,PM5_1,PM6_1").Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="Yes,No" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "Invalid entry; please use the drop-down menu to select a response." .ShowInput = True .ShowError = True End With |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spaces and Data Validation
Vasant, thanks. However, when I use this, the drop down shows "Yes" and "No"
(with the quotes, which I do not want because my formulas reference Yes and No without the quotes). Is there another way to reword this - I tried multiple ways but VBE didn't like the syntax. "Vasant Nanavati" wrote: Try: Formula1:="""Yes"",""No""" -- Vasant "Paige" wrote in message ... I put VBE code in for my data validation, example is below (it uses range names). However, if the user enters a response manually (without using the drop down menu), the code doesn't reject extra spaces entered. So when I really need "Yes" for example, it takes "Yes " or " Yes". Am I doing something wrong in my code? If so, how can I fix this so that the user cannot add extra spaces (by adding the Application.Trim somewhere)? I have a large workbook with multiple areas affected, that is almost in its last stages of finalization (hopefully), so am praying this is a relatively easy thing to fix. Thanks for any help....Paige Example is: With Range("PM1_1,PM3_1,PM4_1,PM5_1,PM6_1").Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="Yes,No" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "Invalid entry; please use the drop-down menu to select a response." .ShowInput = True .ShowError = True End With |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spaces and Data Validation
You can add something like this. I have not tested it so it may not be exact
Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range On Error GoTo ErrorHandler Application.EnableEvents = False Set rng = Intersect(Target, Range("PM1_1,PM3_1,PM4_1,PM5_1,PM6_1")) If Not rng Is Nothing Then Target.Value = Trim(Target.Value) ErrorHandler: Application.EnableEvents = True End Sub This code needs to be pasted into the appropriate worksheet. HTH "Paige" wrote: Vasant, thanks. However, when I use this, the drop down shows "Yes" and "No" (with the quotes, which I do not want because my formulas reference Yes and No without the quotes). Is there another way to reword this - I tried multiple ways but VBE didn't like the syntax. "Vasant Nanavati" wrote: Try: Formula1:="""Yes"",""No""" -- Vasant "Paige" wrote in message ... I put VBE code in for my data validation, example is below (it uses range names). However, if the user enters a response manually (without using the drop down menu), the code doesn't reject extra spaces entered. So when I really need "Yes" for example, it takes "Yes " or " Yes". Am I doing something wrong in my code? If so, how can I fix this so that the user cannot add extra spaces (by adding the Application.Trim somewhere)? I have a large workbook with multiple areas affected, that is almost in its last stages of finalization (hopefully), so am praying this is a relatively easy thing to fix. Thanks for any help....Paige Example is: With Range("PM1_1,PM3_1,PM4_1,PM5_1,PM6_1").Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="Yes,No" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "Invalid entry; please use the drop-down menu to select a response." .ShowInput = True .ShowError = True End With |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spaces and Data Validation
Hi Paige,
How about adding a worksheet_change event handler as follows in addition to your existing data validation. Dim rng As Range Set rng = Intersect(Target, Union(Sheet1.Range("MyTest"), Sheet1.Range("MyTest2"))) If Not rng Is Nothing Then rng.Cells(1, 1).Value = Trim(rng.Cells(1, 1).Value) End If Here MyTest and MyTest2 are range names and you can replace them with your range names. I know this is not the best solution.. as you would have like to modify the DV itself. Alok Joshi "Paige" wrote: I put VBE code in for my data validation, example is below (it uses range names). However, if the user enters a response manually (without using the drop down menu), the code doesn't reject extra spaces entered. So when I really need "Yes" for example, it takes "Yes " or " Yes". Am I doing something wrong in my code? If so, how can I fix this so that the user cannot add extra spaces (by adding the Application.Trim somewhere)? I have a large workbook with multiple areas affected, that is almost in its last stages of finalization (hopefully), so am praying this is a relatively easy thing to fix. Thanks for any help....Paige Example is: With Range("PM1_1,PM3_1,PM4_1,PM5_1,PM6_1").Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="Yes,No" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "Invalid entry; please use the drop-down menu to select a response." .ShowInput = True .ShowError = True End With |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spaces and Data Validation
Thanks everyone. I will try these today and see if I can make them work.
Will come back and post something, to let others that come after know the results (i.e., if I can make them work!!!). Thanks for your help....Paige "Alok" wrote: Hi Paige, How about adding a worksheet_change event handler as follows in addition to your existing data validation. Dim rng As Range Set rng = Intersect(Target, Union(Sheet1.Range("MyTest"), Sheet1.Range("MyTest2"))) If Not rng Is Nothing Then rng.Cells(1, 1).Value = Trim(rng.Cells(1, 1).Value) End If Here MyTest and MyTest2 are range names and you can replace them with your range names. I know this is not the best solution.. as you would have like to modify the DV itself. Alok Joshi "Paige" wrote: I put VBE code in for my data validation, example is below (it uses range names). However, if the user enters a response manually (without using the drop down menu), the code doesn't reject extra spaces entered. So when I really need "Yes" for example, it takes "Yes " or " Yes". Am I doing something wrong in my code? If so, how can I fix this so that the user cannot add extra spaces (by adding the Application.Trim somewhere)? I have a large workbook with multiple areas affected, that is almost in its last stages of finalization (hopefully), so am praying this is a relatively easy thing to fix. Thanks for any help....Paige Example is: With Range("PM1_1,PM3_1,PM4_1,PM5_1,PM6_1").Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="Yes,No" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "Invalid entry; please use the drop-down menu to select a response." .ShowInput = True .ShowError = True End With |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spaces and Data Validation
Just as a follow up, Vasant's recommendation worked except that the options
now showed up with apostrophes, which for my purposes wouldn't be feasible at this point since I am so far along in the process. Jim and Alok's suggestions both worked great. Thanks to all three of you for your assistance - I've learned something from each!! "Paige" wrote: Thanks everyone. I will try these today and see if I can make them work. Will come back and post something, to let others that come after know the results (i.e., if I can make them work!!!). Thanks for your help....Paige "Alok" wrote: Hi Paige, How about adding a worksheet_change event handler as follows in addition to your existing data validation. Dim rng As Range Set rng = Intersect(Target, Union(Sheet1.Range("MyTest"), Sheet1.Range("MyTest2"))) If Not rng Is Nothing Then rng.Cells(1, 1).Value = Trim(rng.Cells(1, 1).Value) End If Here MyTest and MyTest2 are range names and you can replace them with your range names. I know this is not the best solution.. as you would have like to modify the DV itself. Alok Joshi "Paige" wrote: I put VBE code in for my data validation, example is below (it uses range names). However, if the user enters a response manually (without using the drop down menu), the code doesn't reject extra spaces entered. So when I really need "Yes" for example, it takes "Yes " or " Yes". Am I doing something wrong in my code? If so, how can I fix this so that the user cannot add extra spaces (by adding the Application.Trim somewhere)? I have a large workbook with multiple areas affected, that is almost in its last stages of finalization (hopefully), so am praying this is a relatively easy thing to fix. Thanks for any help....Paige Example is: With Range("PM1_1,PM3_1,PM4_1,PM5_1,PM6_1").Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="Yes,No" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "Invalid entry; please use the drop-down menu to select a response." .ShowInput = True .ShowError = True End With |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
custom data validation on cells with data validation values | Excel Worksheet Functions | |||
Spaces at the end of data | Excel Worksheet Functions | |||
Data Validation, Need text any length no spaces | Excel Discussion (Misc queries) | |||
how do I remove leading spaces and leave the remianing spaces w | Excel Worksheet Functions | |||
Spaces in Data | New Users to Excel |