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" 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