Spaces and Data Validation

[Guest]

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

 

[Vasant Nanavati]

Try:

Formula1:="""Yes"",""No"""

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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!!