Changing Entries to Proper and Upper Case Q

  • Thread starter Thread starter Sean
  • Start date Start date
S

Sean

I am trying to Capitalise all values (ucase) in a certain range of
cells, see A values below and changing other values, see C and M
below, to proper case.

These woulkd be changed on entry in to the various cells, but its not
working. The original typed values are uneffected, can anyone suggest
why?



Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count = 1 Then
If Not Intersect(.Cells,
Range("C18,M18,C23,M23,C28,M28,C33,M33,C38,M38,C43,M43,C48,M48,C53,M53,C58,M58,C63,M63"))
Is Nothing Then
Application.EnableEvents = False
.Value = Application.Proper(.Value)
Application.EnableEvents = True
End If
End If
End With

With Target
If .Count = 1 Then
If Not Intersect(.Cells,
Range("A18,A23,A28,A33,A38,A43,A48,A53,A58,A63")) Is Nothing Then
Application.EnableEvents = False
.Value = Application.UCase(.Value)
Application.EnableEvents = True
End If
End If
End With
End Sub
 
It is capitalizing only the first letter of the word try replacing
from:
..Value = Application.UCase(.Value)
to:
.Value = StrConv(.Value, vbUpperCase)
 
Your code appears to work. Just run:

Sub demo()
Application.EnableEvents = True
End Sub

first
 
You may be locked so use this
Sub fixit()
Application.EnableEvents = True
End Sub

and then try this without application
..Value = UCase(.Value)
 
You may be locked so use this
Click to expand...
and then try this without application
.Value = UCase(.Value)

--
Don Guillett
SalesAid Software








- Show quoted text -
Click to expand...

Thanks Don, that worked, but what was the -
Sub fixit()
Application.EnableEvents = True
End Sub
 
Back
Top