DEFAULT TO UPPER CASE

[Prasad Gopinath]

How do i default all cells in the spread sheet to upper case?

Prasad

 

[Jim Thomlinson]

You can't really default to upper case but you can force it...

http://www.dailydoseofexcel.com/archives/2006/10/03/force-upper-case/

 

[Gord Dibben]

Turn on Caps Lock or use event code to change to UPPER case as you enter text.

This code operates on all sheets in the workbook.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
ErrHandler:
Application.EnableEvents = True
End Sub

This is event code. Right-click on the Excel logo left of "File" on the menubar
or left end of Title Bar if window is not maximized.

Select "View Code". Copy/paste the code into that Thisworkbook module.

*******************************

To operate on just one worksheet use this code.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
ErrHandler:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code". Copy/paste into that Sheet
module.


Gord Dibben MS Excel MVP

 

[tankerman]

Gord your vb works great it but how can I narrow it down to just one column
(I2:I250). There are several individuals using this sheet and some have less
computer smarts than I do which is not much.

 

[David McRitchie]

at the beginning of your code and BEFORE disabling event handling
add the following line.

if target.column <> 3 then exit sub

additional aspects of changing letter case can be found in
http://www.mvps.org/dmcritchie/excel/proper.htm

 

[Gord Dibben]

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


Gord

 

[tankerman]

Gord, works great, this discussion group has been a life saver since i found
it.