Change case...help please

[Terry]

Win XP Pro
Office 2003

Using "Excel" and wish to select any text in a worksheet to ALTER the case
to either upper or lower.
When using "Word" it is easy via the menu.
Is there a menu driven option in Excel please, as I do not like the idea of
using a function for this purpose.

TIA

 

[Terry]

No thanks...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.

Terry

 

[Gordon]

No thanks...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.

Terry

You have two options.
1. Write some VBA and assign a button to it, or,
2. Re-design Excel.

 

[Terry]

Far behond me......but thanks for the short and to the point answer.

Terry

 

[Max]

...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 A1200
(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)

 

[Max]

Far behond me......but thanks for the short and to the point answer. ....
"Gordon" wrote ....

Ah, no need to write, just source and use what others have written, giving
due credit, of course. See one way where the entire lot has been packaged
nicely <g> in my response in the other thread ..

 

[Terry]

Max.......Thank you for the substantial reply.

Terry

 

[Terry]

Thank you

Ah, no need to write, just source and use what others have written, giving
due credit, of course. See one way where the entire lot has been packaged
nicely <g> in my response in the other thread ..

 

[Max]

You're welcome, Terry !
Hope it's enough to get it working for you <g>
Thanks for the feedback ..

 

[Gary L Brown]

Terry,
I've already incorporated Max's suggestion. It includes using the Proper
function if desired. Max's macro is much simplier to understand but I'll
throw this one out to you. I've used it for quite some time now.
HTH.
--
Gary Brown
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''''Yes'''' button next to
''''Was this Post Helpfull to you?".

'Passed back to the function from the UserForm
Public ChoiceForm_Value As Variant

'/===========================================/
Public Sub SelectCase()
'select a range and wrap UPPER, LOWER or PROPER
' function around it if it's text
Dim aryAnswer(1 To 4) As String
Dim rng As Range, rCell As Range
Dim strSelection As String
Dim strAnswer As String, strType As String

On Error Resume Next

aryAnswer(1) = "Upper Case"
aryAnswer(2) = "Lower Case"
aryAnswer(3) = "Proper Case"
aryAnswer(4) = "Cancel"
strSelection = Selection.Address

Set rng = Application.InputBox( _
prompt:="Select a range on this worksheet", _
Default:=strSelection, _
Type:=8)

strAnswer = udfGetSelection(aryAnswer)

If strAnswer = aryAnswer(4) Then
GoTo exit_Sub
End If

For Each rCell In rng
If TypeName(Application.Intersect(rCell, _
(ActiveSheet.UsedRange))) = "Nothing" Then
Exit For
End If

Select Case strAnswer
Case aryAnswer(1)
If _
WorksheetFunction.IsText(rCell) = _
True Then
If rCell.HasFormula = True Then
rCell.Formula = "=Upper(" & _
Right(rCell.Formula, _
Len(rCell.Formula) - 1) & ")"
Else
rCell.Formula = "=Upper(" & _
Chr(34) & rCell.value & Chr(34) & ")"
End If
End If
Case aryAnswer(2)
If WorksheetFunction.IsText(rCell) = True Then
If rCell.HasFormula = True Then
rCell.Formula = "=Lower(" & _
Right(rCell.Formula, _
Len(rCell.Formula) - 1) & ")"
Else
rCell.Formula = "=Lower(" & _
Chr(34) & rCell.value & Chr(34) & ")"
End If
End If
Case aryAnswer(3)
If WorksheetFunction.IsText(rCell) = True Then
If rCell.HasFormula = True Then
rCell.Formula = "=Proper(" & _
Right(rCell.Formula, _
Len(rCell.Formula) - 1) & ")"
Else
rCell.Formula = "=Proper(" & _
Chr(34) & rCell.value & Chr(34) & ")"
End If
End If
Case Else
Exit Sub
End Select
Next rCell

exit_Sub:
Set rng = Nothing

End Sub
'/===========================================/
Private Function udfGetSelection(aryStr() As String) _
As String
'Adds choices as defined in Ops array below
Dim aryChoices()
Dim iMaxChoices As Long, i As Long
Dim strTitle As String
Dim varChoiceSelected As Variant

On Error Resume Next

iMaxChoices = UBound(aryStr)
strTitle = "Change Case of Text..."

ReDim aryChoices(1 To iMaxChoices)

For i = 1 To iMaxChoices
aryChoices(i) = aryStr(i)
Next i

'Array of choices, default choice,
' title of form
varChoiceSelected = udfChoiceForm(aryChoices, _
iMaxChoices, strTitle)

' MsgBox aryChoices(varChoiceSelected)
udfGetSelection = aryChoices(varChoiceSelected)
End Function
'/===========================================/
Private Function udfChoiceForm(OpArray, Default, Title)
'based on a John Walkenbach program
'Creates a form with Custom Choices
'OpArray= array of choices
'Default= default choice, i.e. 1=1st choice in array
'Title = title of form
Dim TempForm As Object 'VBComponent
Dim NewOptionButton, NewCommandButton1, NewCommandButton2
Dim i As Integer, TopPos As Integer
Dim MaxWidth As Long
Dim Code As String

On Error Resume Next

'Hide VBE window to prevent screen flashing
Application.VBE.MainWindow.Visible = False

'Create the UserForm
'vbext_ct_MSForm
Set TempForm = _
ThisWorkbook.VBProject.VBComponents.Add(3)

TempForm.Properties("Width") = 800

'Add the OptionButtons
TopPos = 4
MaxWidth = 0 'Stores width of widest OptionButton
For i = LBound(OpArray) To UBound(OpArray)
Set NewOptionButton = _
TempForm.Designer.Controls. _
Add("forms.OptionButton.1")
With NewOptionButton
.Width = 800
.Caption = OpArray(i)
.Height = 15
.Left = 8
.Top = TopPos
.Tag = i
.AutoSize = True
If Default = i Then .value = True
If .Width > MaxWidth Then MaxWidth = .Width
End With
TopPos = TopPos + 15
Next i

'/----------Add the OK button-------------
Set NewCommandButton1 = _
TempForm.Designer.Controls. _
Add("forms.CommandButton.1")
With NewCommandButton1
.Caption = "OK"
.Height = 18
.Width = 44
.Left = MaxWidth + 12
.Top = 6
End With
'/-----------------------------------------

'/----------Add the Cancel button----------
Set NewCommandButton2 = _
TempForm.Designer.Controls. _
Add("forms.CommandButton.1")
With NewCommandButton2
.Caption = "Cancel"
.Height = 18
.Width = 44
.Left = MaxWidth + 12
.Top = 28
End With
'/-----------------------------------------

'---Add event-hander subs for the CommandButtons---
Code = ""
Code = Code & "Sub CommandButton1_Click()" & vbCrLf
Code = Code & " Dim ctl" & vbCrLf
Code = Code & " ChoiceForm_Value = False" & vbCrLf
Code = Code & " For Each ctl In Me.Controls" & vbCrLf
Code = Code & " If TypeName(ctl) " & _
"= ""OptionButton"" Then" & vbCrLf
Code = Code & " If ctl Then " & _
"ChoiceForm_Value = ctl.Tag" & vbCrLf
Code = Code & " End If" & vbCrLf
Code = Code & " Next ctl" & vbCrLf
Code = Code & " Unload Me" & vbCrLf
Code = Code & "End Sub" & vbCrLf
Code = Code & "Sub CommandButton2_Click()" & vbCrLf
Code = Code & " ChoiceForm_Value=False" & vbCrLf
Code = Code & " Unload Me" & vbCrLf
Code = Code & "End Sub" & vbCrLf
'/-----------------------------------------

With TempForm.CodeModule
.InsertLines .CountOfLines + 1, Code
End With


'Adjust the form
With TempForm
.Properties("Caption") = Title
.Properties("Width") = NewCommandButton1.Left + _
NewCommandButton1.Width + 10
If .Properties("Width") < 160 Then
.Properties("Width") = 160
NewCommandButton1.Left = 106
NewCommandButton2.Left = 106
End If
.Properties("Height") = TopPos + 34
End With

'Show the form
VBA.UserForms.Add(TempForm.name).Show

'Delete the form
ThisWorkbook.VBProject.VBComponents.Remove _
VBComponent:=TempForm

'Pass the selected option back to
' the calling procedure
udfChoiceForm = ChoiceForm_Value

End Function
'/===========================================/

 

[Terry]

Gary
Big thank you, but far more complex than I thought, I'll stick to manually
altering the case as required, but I do like the way MS Word does it.

Terry

 

[Terry]

Thanks all posters, and wish to update this group:
Shailesh Shah provided the answer I was looking for with her "ADD-IN",
posted here 1/10/05.

Terry

 

[Terry]

Correction:

Shah did not reply via this NG, as stated in my earlier update post.(email)

Terry

 

[Terry]

I have tried a small com-addin for changing case in XL at the following
address, and works a treat:
http://www.members.lycos.co.uk/shahweb/

Terry