Forcing uppercase, proper case

[Peter Bishop]

Does anyone know how to format all the cells in a column
to convert everything to UPPER CASE or Proper Case,
regardless of how text is entered?

I've set up a spreadsheet for storing names and
addresses. I want to be able to type in lower case and
have the names converted to proper case ("John Doe") and
suburbs converted to upper case "(WAIKIKI") without having
to mess around with the Shift or Caps Lock keys.

 

[Nick Hodge]

Peter

The following code placed in the worksheet code module (Right click on sheet
tab and select 'View code...') will look at any entry in column A and change
it to proper on entry. It will look at any entry in column B and change it
to upper on entry, It also resizes the column 'to fit'

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo EventEnable
Application.EnableEvents = False
If Not Intersect(Target, Columns("A:A")) Is Nothing Then
Target.Value = Application.WorksheetFunction.Proper(Target.Value)
Target.Columns.AutoFit
End If
If Not Intersect(Target, Columns("B:B")) Is Nothing Then
Target.Value = UCase(Target.Value)
Target.Columns.AutoFit
End If
Application.EnableEvents = True
Exit Sub

EventEnable:
Application.EnableEvents = True
Exit Sub
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Peter Bishop]

I made a mistake with my email address. It should be:

(e-mail address removed)

Peter Bishop