Max -
I found this very helpful, however I only need the sub to convert to all
caps so I tried to eliminate the other stuff and did not work.
can you tweek the sub below to only convert to all caps and i would also
appreciate step by step how to assign macro to an auto shape or button.
you say right-click on autoshape - assign macro - Excel2000 doesn't say
'assign macro' after I right click say the 3-D box- also in Form tool bar -
assign sub to a button - didn't let me customize a button - either one is ok
- please give me step by step. I have done the button one years ago and no
longer have any books to refer to.
thanking you in advance.
--
claudia
"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