Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing file in all upper case to upper and lower case | Excel Discussion (Misc queries) | |||
excel'03 how to convert a column from upper case to proper case | Excel Discussion (Misc queries) | |||
Excel: How do I change all upper case ss to proper case? | Excel Worksheet Functions | |||
Changing Upper case to Proper Case | Excel Worksheet Functions | |||
changing lower case to upper case | Excel Discussion (Misc queries) |