another: CAPS, can you preset a column (States?)

J

jacob farino

can you preset a column to display it's contents in all caps? More
specifically, I have a column where users enter in state abbreivations
(i.e., GA or TX); does anyone have a useful, easy way to do this?

Thank-you!!!
 
C

Charlie O'Neill

Jacob,

One possible way would be to add the desired results to the Auto Correct
feature under the Tools menu.

Charlie O'Neill
 
J

jacob farino

Thanks Charles!
This works for all states EXCEPT states that begin with "i"...any
suggestions?
 
G

Gord Dibben

Jacob

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

Right-click on your sheet tab and copy/paste the above code into the module.

Column A(1) will be all CAPS as you enter.

Gord Dibben Excel MVP
 
J

jacob farino

Gord:

That works perfectly, thank-you!!! Now, being the amateur that I am, I don't
see a simple way to alter which column the macro affects (technically, I
need column C to be auto-uppercased), and instead of me screwing around
inside your module, I figured I'd ask.

Jacob
 
G

Gord Dibben

Jacob

Change the Target.Column line to read.....

If Target.Column <> 3 Then Exit Sub

Which means...if you are not in Column C, don't run the code.

Gord
 
Top