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