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

Hi Sir this is Gio from Philippines...... it's my first time here in
this site... i am wondering how can i solve this problem in excel and please
help me.. I used excel 2000 in creating an inventory program in the hospital.
I used this excel inventorry program in our suppply room, i used one
worksheet per item. and i have almost 300 items in the supply room or almost
300 worksheets. I saved it as a template for all i know it is safer to save
it as template rather than saving it as ordinary excel files. The program was
working well, but not when i started linking(hyperlink) it from a certain
file that i always used. Then i have save it several times as a template but
i notice that the program malfuncitons, it doesnt compute the formulas i
created and some formulas are gone. Why is this happening. when i add some
items in the inventory it wouldnt add to the current balance, why is this
happening? Will you please help me, you wer the only people who can only
help me with this kind of problem......please....

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

Bob,

Excellent. Great help. Thank you very much. Exactly what I was looking for.
This reduces my menial tasks to a great extent at work.

Bob, I had posted another question in this forum under the heading 'Copy
Name and email address from web sites to excel sheet' in Excel category some
time ago. I haven't received any help regarding the same. It is a part of the
same task I want to complete at work. If you could please look into it as
well and help. Probably there could be a small VB program itself that could
run as a service and be a part of the right click options in Internet
Explorer that transfers the selected text to an excel file. The excel file
acts as a database with two fields:

Name Email Address

Philip
Bob

And the new details gets added onto the excel sheets as I right click in
Internet explorer and choose whether the selected text goes into the first or
second field of the open workbook. The program should also contain an option
to change the workbook name and location from time to time.

I hope I am not asking too much.

Once again, I appreciate your help and thanks.

"Bob Phillips" wrote:

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