ExcelBanter - Changing Entries to Proper and Upper Case Q

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Changing Entries to Proper and Upper Case Q (https://www.excelbanter.com/excel-worksheet-functions/137428-changing-entries-proper-upper-case-q.html)

Changing Entries to Proper and Upper Case Q

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

Changing Entries to Proper and Upper Case Q

It is capitalizing only the first letter of the word try replacing
from:
..Value = Application.UCase(.Value)
to:
.Value = StrConv(.Value, vbUpperCase)

"Sean" wrote:

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

Changing Entries to Proper and Upper Case Q

Your code appears to work. Just run:

Sub demo()
Application.EnableEvents = True
End Sub

first
--
Gary''s Student - gsnu200713

Changing Entries to Proper and Upper Case Q

On Apr 1, 2:01 pm, "Don Guillett" wrote:
You may be locked so use this

and then try this without application
.Value = UCase(.Value)

--
Don Guillett
SalesAid Software
"Sean" wrote in message

oups.com...

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- Hide quoted text -

- Show quoted text -

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