Philip,

Here it is, completely revised.

What I have done is to create a context menu as you asked, but I took out
the input box, and added all of the options to the context menu. Sentence
case works properly as well now

Put this code in a standard code module

Private Sub ChangeCase()
Dim cell As Range
Dim aryParts
Dim iPos As Long

For Each cell In Selection
With cell
If Not .HasFormula Then
Select Case Application.CommandBars.ActionControl.Parameter
Case "Upper": .Value = UCase(.Value)
Case "Lower": .Value = LCase(.Value)
Case "Proper": .Value = Application.Proper(.Value)
Case "Sentence": .Value = SentenceCase(.Value)
End Select
End If
End With
Next cell
End Sub

Private Function SentenceCase(ByVal para As String) As String
Dim oRegExp As Object
Dim oMatch As Object
Dim oAllMatches As Object

para = LCase(para)
Set oRegExp = CreateObject("VBScript.RegExp")
oRegExp.Pattern = "^[a-z]|\.( )*[a-z]"
oRegExp.Global = True
Set oAllMatches = oRegExp.Execute(para)
For Each oMatch In oAllMatches
With oMatch
Mid(para, .FirstIndex + 1 + .Length - 1, 1) = _
UCase(Mid(para, .FirstIndex + 1 + .Length - 1, 1))
End With
Next oMatch
SentenceCase = para
End Function


put this code in the ThisWorkbook code module

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next 'just in case
Application.CommandBars("Cell").Controls("Case Changer").Delete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
Dim oCtl As CommandBarControl

On Error Resume Next 'just in case
Application.CommandBars("Cell").Controls("Case Changer").Delete
On Error GoTo 0

With Application.CommandBars("Cell")
With .Controls.Add(Type:=msoControlPopup, temporary:=True)
.BeginGroup = True
.Caption = "Case Changer"

With .Controls.Add
.Caption = "Upper case"
.OnAction = "ChangeCase"
.Parameter = "Upper"
End With
With .Controls.Add
.Caption = "Lower case"
.OnAction = "ChangeCase"
.Parameter = "Lower"
End With
With .Controls.Add
.Caption = "Proper case"
.OnAction = "ChangeCase"
.Parameter = "Proper"
End With
With .Controls.Add
.Caption = "Sentence case"
.OnAction = "ChangeCase"
.Parameter = "Sentence"
End With
End With
End With

End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

--
HTH

Bob Phillips

"Philip" wrote in message
...
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