how can i make a cell always show text in caps

T

thomas donino

I want to set up certain columns in my spreasheet so that all text entered
into it shows in caps
 
J

JE McGimpsey

If you have a formula, use the UPPER() function. Assuming your cell is
user-entered, you'd need an event macro. For example. put this in your
worksheet code module (right-click on the worksheet tab and choose View
Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Range("A1")
If Not Intersect(Target, .Cells) Is Nothing Then
Application.EnableEvents = False
.Value = UCase(.Value)
Application.EnableEvents = True
End If
End With
End Sub
 
J

JE McGimpsey

Two quibbles -

This code assumes that there's only one cell selected. If Range("Input")
was defined as cell J1, and cells A1:J10 were selected, cell A1 would
be coerced to upper case, not J1.

Also, without disabling events, assigning the value will create a
recursive call to Worksheet_Change, which will terminate when XL runs
out of stack space. Fortunately, XL terminates nicely, in that case, but
it's not good practice.
 
Top