Capitalizing selected area

J

J.E

Is the correct verb 'to capitalize' ? :D

Anyway, how do I make letters in selected
area go CAPS? Its a workbook that is going
to be filled up in daily basis by novice users
and input should always be in capital letters.

Thank you.
 
H

Harald Staff

Hi

You need a small macro for that:

Sub Capitalize()
Dim Cel As Range
For Each Cel In Selection
Cel.Formula = UCase$(Cel.Formula)
Next
End Sub

HTH. Best wishes Harald
 
G

Gord Dibben

As the text is entered it shall be changed to UPPER.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column > 2 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
ErrHandler:
Application.EnableEvents = True
End Sub

Operates on columns A and B

To operate on a range use

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Application.Intersect(Range("A1:A20"), Target) Is Nothing Then
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
End If
ErrHandler:
Application.EnableEvents = True
End Sub

Note: this is sheet event code and must go behind the worksheet.

Right-click on sheet tab and "View Code".

Paste one of the above into that sheet module.


Gord Dibben Excel MVP
 
Top