How to set Upper case in a column

  • Thread starter Thread starter ilyaz
  • Start date Start date
I

ilyaz

I have a spread sheet set up in a MS Excel 2002. I know there is a formula
=UPPER. I can use it for any individual cell. But I'd like to use this
formula in a few columns, so the text in each cell of these columns will be
in UPPER case.
May someone please tell me how to use formula within a column? Step-by-step
instructions will be appreciated.
 
I do NOT recommend you do this but if you insist,

Right click sheet tab>view code>copy/paste this>change column K to suit>SAVE
workbook.
Now anything you type into that column will be changed to all upper.
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("k")) Is Nothing Then Exit Sub
Target = UCase(Target)
End Sub
 
Don,
thanks for your suggestion. May I ask you, why you <do NOT recommend> it?
Ilya

Don Guillett said:
I do NOT recommend you do this but if you insist,

Right click sheet tab>view code>copy/paste this>change column K to
suit>SAVE workbook.
Now anything you type into that column will be changed to all upper.
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("k")) Is Nothing Then Exit Sub
Target = UCase(Target)
End Sub
Click to expand...
 
Just like in the newsgroups where it is considered to be shouting and it IS
harder to read. I only use for EMPHASIS.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
ilyaz said:
Don,
thanks for your suggestion. May I ask you, why you <do NOT recommend> it?
Ilya
Click to expand...
 
Oh, I see. However, in my case, in the <last name> column, I have to spell
out all the entries in upper case. So it was a pain to enter a formula first
or remember to keep caps lock pressed etc.
Thanks again.
Ilya
 
Well, Don, I knew that I'm not good with Excel, but I didn't know that I'm
so bad...
OK, I used your macro and got one column in upper case. Now, I'd like to set
another column to do the same. I tried to set up each column separately -
neither column worked. How to insert more than one column in your macro?
Ilya
 
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then
Exit Sub 'only one cell at a time
End If

If Intersect(Target, Me.Range("k1,x1,z1").EntireColumn) Is Nothing Then
Exit Sub
End If

Application.EnableEvents = False
Target.Value = UCase(Target.Value)
Application.EnableEvents = True

End Sub

I looked at columns K, X and Z. Adjust as necessary.
 
Hi,

Here is a little code you could use:

Sub UpperCase()
Dim cell As Range
For Each cell In Selection
cell = UCase(cell)
Next cell
End Sub

1. Press Alt+F11 (to open the VB Editor)
2. In the Project window, top left, choose your file.
3. Choose Insert, Module
4. Paste the code above into the code window on the right.
5. Close the VB Editor and return to your spreadsheet
6. Choose Tools, Macro, Macros and select your macro and choose Options
7. Assign a shortcut key - best might be upper case U, (hold down the Shift
key and type u.)
8. Click OK, Cancel.

Now you select any range in your spreadsheet and press Ctrl+Shift+U.
 
Shane

Probably of no significance in OP's instance but if the selected range were
to contain any cells with formulas, they would be converted to values.

Better to allow for that.

Sub UpperCase()
Dim cell As Range
For Each cell In Selection
If Not cell.HasFormula Then
cell = UCase(cell)
End If
Next cell
End Sub


Gord Dibben MS Excel MVP
 
Shane and Gord,

I tried your macros, but for some reason it got me in trouble :-(
As soon as I clicked on Ctrl+Shift+U, the pointer changed to a flickering
hourglass which continued to flicker indefinitely. After a few minutes, I
wanted to stop it, but even Task manager could not kill it (first TM would
not open, but when it opened it took me 3 times to kill each).
Anyway, thanks for trying to help me.
Ilya
 
Back
Top