VBA for converting data into various CASES.-write such programmes in VBA

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

One way is to try the sub SwitchCase* below,
which enables one to toggle the case of selected cells
*sub posted by Frank Isaacs '97

Steps
--------
1. Press Alt + F11 to go to VBE
2. Click Insert > Module
3. Copy > Paste the sub below
(everything between the dotted lines)
into the white empty space in the right-side window

-------begin vba------

Sub SwitchCase()
'Frank Isaacs May '97
'Select the cells and run
'Cells' case toggle order:
'if Lower > Upper
'if Upper > Proper
'if Proper** > Lower
'**or neither of the 3 case types

Dim Cell As Range

For Each Cell In Selection
Select Case True
Case Cell = LCase(Cell) 'It's lowercase
Cell = UCase(Cell)
Case Cell = UCase(Cell) 'It's uppercase
Cell = Application.Proper(Cell)
Case Else 'It's neither upper nor lower
Cell = LCase(Cell)
End Select
Next

End Sub

-------end vba------

4. Press Alt + Q to return to Excel

Running the macro
----------------------
5. Select the range of cells, e.g.: select A1:D200
(Or, to select entire sheet, press Ctrl + A.
The sub will work even on discontiguous selections/ranges.)

6. Press Alt + F8 (or click Tools > Macro > Macros)

In the dialog box:
Click on "SwitchCase" > Run
(or just double-click on "SwitchCase")

7. When complete, the text-case of the
selected cells will be converted
depending on their existing case, viz.:

Cells' case toggle order:
----------------------------------
if Lower > Upper
if Upper > Proper
if Proper* > Lower
*or neither of the 3 case types

To further toggle the case,
just run / re-run the sub over on the selected range(s)

---------------------
To make it easier to run / re-run the sub,
we could also assign the sub to a button drawn
from the Forms* toolbar in the sheet
(*If necesssary, activate toolbar via View > Toolbars > Forms)

Or, assign to an autoshape drawn in the sheet
(via right-click on autoshape > Assign Macro)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Philip" <[email protected]> wrote in message
news:[email protected]...
> 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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

Thanks Max

Now I know we can assign macros to command buttons. Great help.

"Max" wrote:

> One way is to try the sub SwitchCase* below,
> which enables one to toggle the case of selected cells
> *sub posted by Frank Isaacs '97
>
> Steps
> --------
> 1. Press Alt + F11 to go to VBE
> 2. Click Insert > Module
> 3. Copy > Paste the sub below
> (everything between the dotted lines)
> into the white empty space in the right-side window
>
> -------begin vba------
>
> Sub SwitchCase()
> 'Frank Isaacs May '97
> 'Select the cells and run
> 'Cells' case toggle order:
> 'if Lower > Upper
> 'if Upper > Proper
> 'if Proper** > Lower
> '**or neither of the 3 case types
>
> Dim Cell As Range
>
> For Each Cell In Selection
> Select Case True
> Case Cell = LCase(Cell) 'It's lowercase
> Cell = UCase(Cell)
> Case Cell = UCase(Cell) 'It's uppercase
> Cell = Application.Proper(Cell)
> Case Else 'It's neither upper nor lower
> Cell = LCase(Cell)
> End Select
> Next
>
> End Sub
>
> -------end vba------
>
> 4. Press Alt + Q to return to Excel
>
> Running the macro
> ----------------------
> 5. Select the range of cells, e.g.: select A1:D200
> (Or, to select entire sheet, press Ctrl + A.
> The sub will work even on discontiguous selections/ranges.)
>
> 6. Press Alt + F8 (or click Tools > Macro > Macros)
>
> In the dialog box:
> Click on "SwitchCase" > Run
> (or just double-click on "SwitchCase")
>
> 7. When complete, the text-case of the
> selected cells will be converted
> depending on their existing case, viz.:
>
> Cells' case toggle order:
> ----------------------------------
> if Lower > Upper
> if Upper > Proper
> if Proper* > Lower
> *or neither of the 3 case types
>
> To further toggle the case,
> just run / re-run the sub over on the selected range(s)
>
> ---------------------
> To make it easier to run / re-run the sub,
> we could also assign the sub to a button drawn
> from the Forms* toolbar in the sheet
> (*If necesssary, activate toolbar via View > Toolbars > Forms)
>
> Or, assign to an autoshape drawn in the sheet
> (via right-click on autoshape > Assign Macro)
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
> "Philip" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

You're welcome, Phillip !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Philip" <[email protected]> wrote in message
news:[email protected]...
> Thanks Max
>
> Now I know we can assign macros to command buttons. Great help.

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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 [email protected]
Bob [email protected]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > >
> > >
> > >
>
>
>