Thanks Bob

Bob if you could please look into the sentence case part of the code. It
doesn't seem to be doing it's job. The sentence doesn't have a capital letter
to start with and I don't think the periods are taken into consideration
after which again a check should be repeated. Please review and help.

Also, is there a way I can assign this macro into the right click pop up
options after selecting a range instead of te command button.

Thnaks again
Was very helpful.

"Bob Phillips" wrote:

Philip,

Here is a simple procedure. Select the cells then run it, it should be
self-explanatory.

Sub ChangeCase()
Dim ans
ans = InputBox("Which type:" & vbNewLine & _
"1 - Upper case" & vbNewLine & _
"2 - Lower case" & vbNewLine & _
"3 - Proper Case" & vbNewLine & _
"4 - Sentence case")
If ans = 1 Or ans = 2 Or ans = 3 Or _
ans = 4 Then
ChangeData CLng(ans)
Else
MsgBox "Invalid selection, try again with a value 1-4"
End If
End Sub

Private Sub ChangeData(pzType As Long)
Dim cell As Range

For Each cell In Selection
With cell
If Not cell.HasFormula Then
Select Case pzType
Case 1: .Value = UCase(.Value)
Case 2: .Value = LCase(.Value)
Case 3: .Value = Application.Proper(.Value)
Case 4:
If Len(.Value) 0 Then
.Value = Left(.Value, 1) & _
LCase(Right(.Value, Len(.Value) - 1))
End If
End Select
End If
End With
Next cell
End Sub

--
HTH

Bob Phillips

"Philip" wrote in message
...
Hello Friends

There is an immediate need at my work place where I need to convert huge
amount of data into different cases like proper, lower, upper and
sentence.
If there is any freeware that lets me do all of the above please advice. I
would also love to know how to write such programmes in VBA for Excel as
an
interest(specially sentence case).

Thank you

Philip Jacob
Senior Executive Quality Appraisal
First American Corporation