Forcing Caps

  • Thread starter Thread starter ianripping
  • Start date Start date
I

ianripping

Is it possible to make all the cells in one column convert any tex
entered into them to capitals?

Example 1

I type

hello
my
name
is
ian

into column A

then it converts all these into

HELLO
MY
NAME
IS
IAN

in column A
 
Hi

Check out this WebPages

http://www.mvps.org/dmcritchie/excel/proper.htm
Or
http://www.cpearson.com/excel/case.htm

Here is a macro for changing text cells in the selection

Sub Uppercase_macro()
Dim selectie As Range
Dim cel As Range
On Error Resume Next
Set selectie = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
If selectie Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each cel In selectie
cel.Value = UCase(cel.Value)
Next cel
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub


If you want to change the text in the cells to Uppercase when you Enter text you can use a event.
This will work only in Column A.
You must place this event in a sheet module

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
If Target.Column = 1 Then
For Each cell In Target.Cells
With cell
If Not .HasFormula And Not IsNumeric(.Value) Then
Application.EnableEvents = False
.Value = UCase(.Value)
Application.EnableEvents = True
End If
End With
Next cell
End If
End Sub
 
Because I say so said:
If Target.Column = 1 Then
Click to expand...


This is working for the whole sheet

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
For Each cell In Target.Cells
With cell
If Not .HasFormula And Not IsNumeric(.Value) Then
Application.EnableEvents = False
.Value = UCase(.Value)
Application.EnableEvents = True
End If
End With
Next cell
End Sub
 
Hi Ian

It only works in Column A because that is the range Ron set
If you changed this to
If Target.Column < 5 Then

for example, then it would work in columns a,b,c,d.
 
Back
Top