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 philip@yahoo.co.uk
Bob bob@yahoo.com

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" <Philip@discussions.microsoft.com> wrote in message
> news:82ACB36B-E174-498B-880D-2D7C28798047@microsoft.com...
> > 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" <Philip@discussions.microsoft.com> wrote in message
> > > news:DC881F7E-EC1C-4FC0-A7FA-E169772A6757@microsoft.com...
> > > > 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
> > >
> > >
> > >
>
>
>