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