Changing Entries to Proper and Upper Case Q

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
 
J

Joel

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

Gary''s Student

Your code appears to work. Just run:

Sub demo()
Application.EnableEvents = True
End Sub

first
 
D

Don Guillett

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

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

Sean

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

--
Don Guillett
SalesAid Software








- Show quoted text -

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top