Terry said:
...do not wish to use any formulae for this task, as I stated I
wish to be able to select single or multiple text entries, then have an
OPTION to change the case to whatever I need.
One way - 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
200
(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)
