changing format to all capitals

  • Thread starter Thread starter Iain Greig
  • Start date Start date
I

Iain Greig

Hi,
Is there a way to change the format of a spreadsheet to all capitals.
I have to make a list from 5 or 6 txt documents but would rather they where
all listed in capitals.
thnx.
 
Hi Iain!

Here's some code from David McRitchie.

Sub Upper_Case()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim Cell as Range
On Error Resume Next 'In case no cells in selection
For Each Cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
Cell.Formula = UCase(Cell.Formula)
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Select the range that you want to apply this to and run the
subroutine.

But I can't recommend use of all upper case as it is more difficult to
read especially if your first language isn't the one used.

For more text changing code see:

http://www.mvps.org/dmcritchie/excel/proper.htm#upper

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi, just tried sokevin solution, doesnt work. Keep getting circular
reference errors.
@Norman, how exactly do I run this code, is it a Macro?
thnx.
Iain
 
You might want to have this in your personal.xls

Sub ChangeCase()
Application.ScreenUpdating = False
Dim r As Range
nCase = UCase(InputBox("Enter U for UPPER" & Chr$(13) & " L for
lower" & Chr$(13) & " Or " & Chr$(13) & " P for Proper", "Select
Case Desired"))
Select Case nCase
Case "L"
For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = LCase(r.Formula)
'R.Formula = R.Value
Else
r.Value = LCase(r.Value)
End If
Next

Case "U"
For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = UCase(r.Formula)
'R.Formula = R.Value
Else
r.Value = UCase(r.Value)
End If
Next
Case "P"

For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = Application.Proper(r.Formula)
'R.Formula = R.Value
Else
r.Value = StrConv(r.Value, vbProperCase)
End If
Next
End Select
Application.ScreenUpdating = True
End Sub
 
Hi Iain!

Copy the code direct from my last post
Right click on one of the sheet tabs to activate the Visual Basic
Editor (VBE)
In the top left window (The Project Explorer)
Click the name of your workbook
Right click > Insert > Module
That inserts a module and you'll see a code widow on the right of the
screen.
Point and click in that window
Right click > paste

Back into Excel
Select the range that you want to change
Tools > Macro > Macros
Select Upper_Case from the list
Click the Run button


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Iain!

If you are likely to want to use the subroutine in other files, you
should copy and paste to your Personal.xls file.

See if you have one first:

Windows > Unhide
Personal should be there if you have it.

If you don't then:

Tools > Macro > Record new macro
Select Personal Macro Workbook from the "Store in" drop down
Make a few format changes
Press the Stop recording button

When you close Excel accept the offer to save changes

Thereafter you can copy and paste subroutines to modules that you
insert into your Personal.xls

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top