Trim Conflicts with Data Validation
 

I have the following data val for a merged cell (E8:F8) with the range name
of SVCTYPE:

Private Sub Worksheet_SelectionChange(ByVal target As Range)
With Range("SVCTYPE").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="MMS,Repair,Convert"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Invalid entry."
.ShowInput = True
.ShowError = True
End With
End Sub

Also have the following, whose intent is to trim any excess spaces in case
the user decides to manually enter the response and accidentally adds spaces
to the end for instance.

Private Sub Worksheet_Change(ByVal target As Range)
If Not Intersect(target, Range("$E$8:$F$8")) Is Nothing Then
target.Value = Trim(target.Value)
End If
End Sub

Problems a 1) If there is an entry in the cell and the user deletes it, I
get a type/mismatch error with the target.Value = Trim(target.value) line of
code. 2) If I use the drop-down to select an entry, the code seems to get
stuck in 'calculating cells' and goes on and on. When I take out the trim
function, everything works fine. Can someone advise what I'm doing wrong?

Trim Conflicts with Data Validation
 

Your events are echoing back and forth. Each chage is causing a change which
is generating an event (near as I can tell). Try suspending events while the
event code is running... for example...

Private Sub Worksheet_Change(ByVal target As Range)
If Not Intersect(target, Range("$E$8:$F$8")) Is Nothing Then
application.enableevents = false 'events wont fire
target.Value = Trim(target.Value)
application.enableevents = true 'events are back on
End If
End Sub




--
HTH...

Jim Thomlinson


"Paige" wrote:

I have the following data val for a merged cell (E8:F8) with the range name
of SVCTYPE:

Private Sub Worksheet_SelectionChange(ByVal target As Range)
With Range("SVCTYPE").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="MMS,Repair,Convert"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Invalid entry."
.ShowInput = True
.ShowError = True
End With
End Sub

Also have the following, whose intent is to trim any excess spaces in case
the user decides to manually enter the response and accidentally adds spaces
to the end for instance.

Private Sub Worksheet_Change(ByVal target As Range)
If Not Intersect(target, Range("$E$8:$F$8")) Is Nothing Then
target.Value = Trim(target.Value)
End If
End Sub

Problems a 1) If there is an entry in the cell and the user deletes it, I
get a type/mismatch error with the target.Value = Trim(target.value) line of
code. 2) If I use the drop-down to select an entry, the code seems to get
stuck in 'calculating cells' and goes on and on. When I take out the trim
function, everything works fine. Can someone advise what I'm doing wrong?

Trim Conflicts with Data Validation
 

Thanks, Jim. Worked great. I had tried turning events off previously, but
hadn't put the code in the right 'sequence' so-to-speak. Sometimes I can't
see the forest for the trees. Have a great weekend.

"Jim Thomlinson" wrote:

Your events are echoing back and forth. Each chage is causing a change which
is generating an event (near as I can tell). Try suspending events while the
event code is running... for example...

Private Sub Worksheet_Change(ByVal target As Range)
If Not Intersect(target, Range("$E$8:$F$8")) Is Nothing Then
application.enableevents = false 'events wont fire
target.Value = Trim(target.Value)
application.enableevents = true 'events are back on
End If
End Sub




--
HTH...

Jim Thomlinson


"Paige" wrote:

I have the following data val for a merged cell (E8:F8) with the range name
of SVCTYPE:

Private Sub Worksheet_SelectionChange(ByVal target As Range)
With Range("SVCTYPE").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="MMS,Repair,Convert"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Invalid entry."
.ShowInput = True
.ShowError = True
End With
End Sub

Also have the following, whose intent is to trim any excess spaces in case
the user decides to manually enter the response and accidentally adds spaces
to the end for instance.

Private Sub Worksheet_Change(ByVal target As Range)
If Not Intersect(target, Range("$E$8:$F$8")) Is Nothing Then
target.Value = Trim(target.Value)
End If
End Sub

Problems a 1) If there is an entry in the cell and the user deletes it, I
get a type/mismatch error with the target.Value = Trim(target.value) line of
code. 2) If I use the drop-down to select an entry, the code seems to get
stuck in 'calculating cells' and goes on and on. When I take out the trim
function, everything works fine. Can someone advise what I'm doing wrong?