Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel cells automatic formatting of text
I use the following data to automatically force cells that have text input
into uppercase or proper case. On the odd occasion I do not want the text input in the listed cells to change into upper case or proper case, but to show exactly as I have typed it in. Is there a way that I can add to the following programming lines that will enable me to do this - e.g. can the input be preceded with some character that prevents the text from taking on upper case or proper case? Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Range("A1,A2,A3")) Is Nothing Then With Target .Value = UCase(.Value) End With ElseIf Not Intersect(Target, Range("B1,B12")) Is Nothing Then With Target .Value = Application.Proper(.Value) End With End If ws_exit: Application.EnableEvents = True End Sub Thanks in advance of any help. John |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel cells automatic formatting of text
Something like:
With Target if left(Target,1)="#" then .value = right(Target,len(Target)-1) else .Value = UCase(.Value) end if End With Should work "John Web" wrote: I use the following data to automatically force cells that have text input into uppercase or proper case. On the odd occasion I do not want the text input in the listed cells to change into upper case or proper case, but to show exactly as I have typed it in. Is there a way that I can add to the following programming lines that will enable me to do this - e.g. can the input be preceded with some character that prevents the text from taking on upper case or proper case? Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Range("A1,A2,A3")) Is Nothing Then With Target .Value = UCase(.Value) End With ElseIf Not Intersect(Target, Range("B1,B12")) Is Nothing Then With Target .Value = Application.Proper(.Value) End With End If ws_exit: Application.EnableEvents = True End Sub Thanks in advance of any help. John |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel cells automatic formatting of text
Hi,
You could preced anything you want left alone with a space and use this code. Note I also changed target.value to target .formula. This ensure formula won't be changed to values should you enter one in the range Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: If Left(Target.Value, 1) = " " Then Exit Sub Application.EnableEvents = False If Not Intersect(Target, Range("A1,A2,A3")) Is Nothing Then With Target .Value = UCase(.Formula) End With ElseIf Not Intersect(Target, Range("B1,B12")) Is Nothing Then With Target .Value = Application.Proper(.Formula) End With End If ws_exit: Application.EnableEvents = True End Sub Mike "John Web" wrote: I use the following data to automatically force cells that have text input into uppercase or proper case. On the odd occasion I do not want the text input in the listed cells to change into upper case or proper case, but to show exactly as I have typed it in. Is there a way that I can add to the following programming lines that will enable me to do this - e.g. can the input be preceded with some character that prevents the text from taking on upper case or proper case? Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Range("A1,A2,A3")) Is Nothing Then With Target .Value = UCase(.Value) End With ElseIf Not Intersect(Target, Range("B1,B12")) Is Nothing Then With Target .Value = Application.Proper(.Value) End With End If ws_exit: Application.EnableEvents = True End Sub Thanks in advance of any help. John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stop automatic formatting of cells (number, text, date, etc) | Excel Worksheet Functions | |||
automatic formatting of cells to currency in Excel 2007 | Excel Worksheet Functions | |||
How can I get automatic row height for merged cells with text wra. | Excel Worksheet Functions | |||
mag-text in merged cells-automatic row heigh | Excel Worksheet Functions | |||
Combining & formatting cells with text - Excel 2002 | Excel Discussion (Misc queries) |