Automatic Capital letter entry

[hinterland_1]

Is it possible to format a cell so that each time a lower case letter i
typed into that cell a upper case letter will appear in a cell
Example, entering Canadian postal codes, type in v2e 1m1 and have i
change to V2E 1M1 automatically. I would perfer not to use macros as
have never used them before

 

[JE McGimpsey]

To do it in the same cell, you'll have to use an Event macro. It's easy:

Put this in the worksheet code module (right-click the worksheet tab and
choose View Code, then paste this into the VBE module that opens):

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

Change the range reference ("A1") to your cell.

If you want more information about macros, see David McRitchie's
"Getting started with macros":

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[John]

But if the Post Code started with 001, for example that code would shorten
to 1, by forcing caps lock - right? - any work around?

 

[David McRitchie]

Hi John (O'Connor),
These are Event macro they fix up the cell upon Entry.
So no numbers that wouldn't have been entered as numbers
will be treated any differently. If you enter a 001 into any
cell that is formatted as General it will produce 1 regardless
of this event change macro.

Canadian zip codes as already mentioned are mixed digits
and letters so they are text. UCASE only affects letters.

But there is a problem with John's (McGimpsey) code, in that
the poster wanted an entire column not just cell A1.

Here is a better example:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'to install -- right-click on the sheettab of the corresponding
' sheet and choose 'view code'. Paste the following procedure
' in the module. -- this is for EVENT macros ONLY.
If Target.Column = 1 Then Exit Sub
If Target.Row = 1 Then Exit Sub
If Target.Column <> 6 Then Exit Sub 'column 6 is column F
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
Application.EnableEvents = True
End Sub

More information on Event macros, see
http://www.mvps.org/dmcritchie/excel/event.htm

Note Event Macros are *not* installed the same as regular
macros.

This macro may appear as Simulating Caps Lock on a column,
but it certainly does not turn on the Caps Lock.

 

[David McRitchie]

This macro would be a bit safer, than my previous suggestion.
The last thing you want is for a change macro to turn off
EnableEvents and not reenable Enable Events when finished,
so this includes an On Error Resume Next

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'to install -- right-click on the sheettab of the corresponding
' sheet and choose 'view code'. Paste the following procedure
' in the module. -- this is for EVENT macros ONLY.
If Target.Column = 1 Then Exit Sub
If Target.Row = 1 Then Exit Sub
If Target.Column = 6 then
on error resume next 'insure that Enable Events gets reenabled
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
Application.EnableEvents = True
on error goto 0
end if
End Sub

If you do have a macro that terminates and leaves EnableEvents as
False you will not be able to run another Event macro until you fix
the problem with a regular macro. See
http://www.mvps.org/dmcritchie/excel/event.htm#problems

 

[JE McGimpsey]

Not sure where the problem is - the OP asked about "that cell"

 

[JE McGimpsey]

No. If there are any alpha characters in the cell (which Canadian Post
Codes have), the leading 0's will be preserved. They will also be
preserved if the cell, as it should be, is formatted as Text.

 

[JE McGimpsey]

Don't really have to run another macro - you can instead type

Application.EnableEvents = True

in the Immediate window of the VBE.

 

[John]

Thanks JE, I tailored my query to a problem I was having, but your post
cleared it up for me on how I should work it