Forcing Capitalization

[Patrick C. Simonds]

It is my desire to be able to enter text into cells and have the text
automatically converted to capital letters. I was given the following two
suggestions (both of which work, to a point):

1. (ActiveCell.Formula=UCase(ActiveCell)
2. Target.Value=UCase(Target.Value)

The problem is that you must exit the cell and then reselect it for
the change to take effect.

Is there any way to make this work so that the cell is converted to all
capitals when you exit the cell, without the time consuming requirement of
reselecting the cell?

[Chip Pearson]

In Excel97, use the Worksheet_Change event procedure.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TheCell As Range
For Each TheCell In Target
If Not Application.Intersect(TheCell, Range("A1:A10")) Is Nothing
Then
TheCell.Value = UCase(TheCell.Value)
End If
Next TheCell
End Sub

For more info about programming Event Procedures, see the "Events"
page on my web site.

Cordially,
Chip Pearson
http://home.gvi.net/~cpearson/excel.htm

Patrick C. Simonds wrote in message
<#6YgmtPG#GA....@uppssnewspub05.moswest.msn.net>...

[Patrick C. Simonds]

This did not solve the problem of having to reselect the cell for the change
to take place.

[David E. Hulme]

In article <eo7oMUXG#GA....@uppssnewspub04.moswest.msn.net>, Patrick C.
Simonds <ordn...@az.com> writes

>This did not solve the problem of having to reselect the cell for the change
>to take place.
>
>

'''''''''''''
Sub auto_open()
' assigns action to the enter key
Application.OnKey "{Enter}", "yourcaps"
End Sub
Sub yourcaps()
' chip pearson's code
End sub
'''''''''''''

--
David E. Hulme

[Chip Pearson]

Patrick,
Try the modified version of Worksheet_Change, below.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TheCell As Range

Application.EnableEvents = False

For Each TheCell In Target

If TheCell.HasFormula = False Then

If Not Application.Intersect(TheCell, Range("A1:A10")) Is
Nothing Then
TheCell.Value = UCase(TheCell.Value)
End If

End If
Next TheCell
Application.EnableEvents = True
End Sub

Cordially,
Chip Pearson
http://home.gvi.net/~cpearson/excel.htm

Patrick C. Simonds wrote in message ...

[Patrick C. Simonds]

Chip just wanted to thank you (David) for your time and energy in creating a
solution to my problem.