Excel cells automatic formatting of text

[John Web]

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

 

[Sam Wilson]

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

 

[Mike H]

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