Text Formatting within a cell

[Rosco]

I know there is a way, I've seen it before I just can't figure it out
or find it online. How in the world would I go about formatting a range
of cells to make all text within that cell proper. I am not talking
about the function "PROPER" but I need the same thing accomplished
without having to pull the information from another cell to make it
"proper" by proper of course i mean the first letter of the word will
be capitalized. I need to be able to enter a first name into a cell and
have that cell auto format the name to capitalize the first letter
regardless of the caps lock state or any capitalization that already
exists?

 

[Guest]

Hi Rosco:

Enter the following macro in worksheet code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim s As String
If Intersect(Target, Columns("A")) Is Nothing Then
Exit Sub
End If

s = Target.Value
s = UCase(Left(s, 1)) & LCase(Right(s, Len(s) - 1))
Target.Value = s
End Sub

It works on cells in column A. If you type a word in column A, the first
letter will be capitalized automatically.


REMEMBER: worksheet code

 

[Bobbie]

Rosco,
I really like the one below. Someone from this site a few months ago
gave this to me. I use it all the time because you highlight the range
and can do 4 different format. Please note this is not my code,
someone from this group furnished it to me


Sub TextConvert() 'Better than mine
'By Ivan F Moala
Dim ocell As Range
Dim Ans As String


Ans = Application.InputBox("Type in Letter" & vbCr & _
"(L)owercase, (U)ppercase, (S)entence, (T)itles ")


If Ans = "" Then Exit Sub


For Each ocell In Selection '.SpecialCells(xlCellTypeConstants, 2)
Select Case UCase(Ans)
Case "L": ocell = LCase(ocell.Text)
Case "U": ocell = UCase(ocell.Text)
Case "S": ocell = UCase(Left(ocell.Text, 1)) & _
LCase(Right(ocell.Text, Len(ocell.Text) - 1))
Case "T": ocell = Application.WorksheetFunction.Proper(ocell.Text)
End Select
Next


End Sub