Proper, and other Text changes -- Use of SpecialCells

Location:	http://www.mvps.org/dmcritchie/excel/proper.htm
Coding:	http://www.mvps.org/dmcritchie/excel/code/proper.txt
Home page:	http://www.mvps.org/dmcritchie/excel/excel.htm
[View without Frames]

If you want the contents of formulas to also be converted use Bill Manville’s code or a variation. (see related below)

This page contains some VBA macros. If you need assistance to install or to use a macro please refer to my «Getting Started with Macros« or delve into it deeper on my Install page.

Coding for this page, is available in another directory with names corresponding to some of the HTML files in the current directory.

A Document Map (Table of Contents of HTML headings H1-Hn) for this page or any other webpage can be generated as a sidebar with a Firefox addon (sorry IE solutions I've seen are not free, nor as elegant, such things may become available for IE7 users later on as it improves)

keywords: capitalization, lettercase, lower case, lowercase, lower-case, proper case, title case, upper case, upper-case, uppercase

«Advantages of a macro:« while you can use the worksheet functions PROPER, UPPER, LOWER to change cells one at at time into another cell it is unlikely that you want to maintain two cells with the same value.   First you had to insert a helper column then carefully use the fill-handle to include all cells but not go too far (usually a double-click). To clean up after worksheet functions you have to remove the dependency on the original by converting to constants (Copy using Ctrl+C then Edit, Paste Special, values) then you can delete the column of original entries. With a macro the change is done in place, you can select entire columns or other selection, invoke the macro and you are finished. A properly written macro will not process empty cells nor cells beyond the UsedRange and that is where the use of SpecialCells comes in.
Warnings: If you have values that are defined as text values by preceding with a quote. The macros regardless of whether they use .formula or .value will select text values based on SpecialCells, but will remove the single quote and text values that look like numbers would then become numbers if the format is General.

SpecialCells -- Usage warning, it has recently come to our attention (2004-01-11) that Special Cells has the following limitation (MS KB 832293). If you select more than 8,192 non-contiguous cells with your macro, as may easily do with SpecialCells, Excel only supports a maximum of 8,192 non-contiguous cells through VBA macros. The inexcusable problem is that there is no error code using VBA. Particularly a problem when deletion or other permanent changes are involved with a selection resulting from SpecialCells. Manually you would at least get: The selection is too large. (May be ranges not cells). Ron de Bruin has written a page on SpecialCells limit problem. Also see Coloring of cells based on CellType which also serves as a Demonstration of Failure in SpecialCells -- if you were deleting rows you could be in big trouble.

A B C

1 -2 #DIV/0! -2

2 #DIV/0! 2 #DIV/0!

3 -4 #DIV/0! -4

You can use this formula to create a lot of discontiguous error cells with: test with Range("A1:F3096")
   =EVEN(ROW())/(ROW()-EVEN(ROW())-COLUMN()+ODD(COLUMN()))
Use this code in intermediate area to select formula cells without errors (or use a subroutine)
   Selection.SpecialCells(xlCellTypeFormulas, 7).select
When you should have more than 8192 areas, all cells will remain selected in original selection area.
I use SpecialCells frequently on this page and on my reformatting (join.htm, Delete Empty Cells/Rows based on Empty
See Coloring of cells based on CellType, also serves as a Demonstration of Failure in SpecialCells.

Sections: #proper. #lower, #upper, #trimsub, #insertblankrows, #intersect, #failure to use Special Cells.

PROPER (#proper)

You can change your all uppercase to PROPER case with a worksheet function. This can help with a list of names and addresses that was in all uppercase (capitals) or lowercase. A mere change to Proper Case will not fix names like “McRitchie” or names like “van der Beck”; therefore, as an example, changes have been made to fix names that begin at the leftmost character of a cell such as those beginning with “Mc”, “Mac”, “O'”, “van der ”, “van ”, “von ”, and “de ”, but not “Mack” because there are too many exceptions to how some people capitalize their name even if spelled the same as someone else. Again these are my exceptions you can code your own exceptions. If there is nothing to the left of the lastname in a cell this is how the folowing names will appear: MacAuley, MacDonald, Mack, Mackey, Mackney, MacManus, MacPherson, Makin, McAndrew, McDonald, McRitchie, Oates, O'Brian, O'Connell, Oddy, O'Hara, Olsen, van Benton, van der Zwan, van Milligen.
If you have numbers indicating a generation, you can process them easily if they follow a comma and make excpetions ", II", ", III", ", IV".
Exceptions for Title Case, are included for embedded words of -- a, and, at, for, from, in, of, on, /and/ the.
HELP --> Index --> PROPER Worksheet Function
=PROPER(a1)
Using a worksheet function you would have to copy then paste special and delete the original afterwards.
A macro works a lot easier. Simply select a range of cells and invoke the Proper_case macro. Additional code is included in the macro to speed up results.
Install the three macros below: the proper_case macro calls the proper_case_inner macro, and CapWords macro is called by the proper_case macro. The code for all three plus some additional related macros can be picked up on the corresponding code/proper.txt page. Manually you invoke the Proper_case macro, i.e. from Alt+F8. The proper_case_inner macro can be called from another macro to use a specified range instead of the current selection. The following macro code can be copied directly from code/proper.txt.
   Sub Proper_case()
      '-- This macro is invoked by you -- i.e. from Macro Dialog (Alt+F8)  
      proper_case_inner    'The macro you invoke from a menu is Proper_Case
   end sub
   Sub Proper_Case_Inner(Optional mySelection As String)
     '-- The parameter here can only be used if invoked from another macro 
     Application.ScreenUpdating = False
     Application.Calculation = xlCalculationManual
     Dim Cell as Range
     Dim Rng As Range
     On Error Resume Next   'In case no cells in selection
     If mySelection =  " " Then Set Rng = Selection _
         Else Set Rng = Range(mySelection)
     For Each Cell In Intersect(Rng, _
            Rng.SpecialCells(xlConstants, xlTextValues))
        Cell.Formula = StrConv(Cell.Formula,vbProperCase)
        '--- this is where you would code generalized changes for lastname
        '--- applied to names beginning in position 1 of cell
        If Left(Cell.Value, 2) = "Mc" Then Cell.Value = _
           "Mc" & UCase(Mid(Cell.Value, 3, 1)) & Mid(Cell.Value, 4, 99)
        If Left(cell.Value, 3) =  "Mac " _
               And Left(cell.Value, 4) <>  "Mack " Then cell.Value = _
            "Mac " & UCase(Mid(cell.Value, 4, 1)) & Mid(cell.Value, 5, 99)
           '-- do not change Mack   Mackey  Mackney  or any Mack...
        If Left(cell.Value, 2) = "O'" Then cell.Value = _
           "O'" & UCase(Mid(cell.Value, 3, 1)) & Mid(cell.Value, 4, 99)
        If Left(cell.Value, 8) = "Van Den " Then cell.Value = _
            "van den  " & Mid(cell.Value, 9, 99)
        If Left(cell.Value, 8) = "Van Der " Then cell.Value = _
           "van der " & Mid(cell.Value, 9, 99)
        '-- single parts after those with two part prefixes
        If Left(cell.Value, 3) =  "Vd " Then cell.Value = _
            "vd " & Mid(cell.Value, 4, 99)
        If Left(cell.Value, 4) =  "V/D " Then cell.Value = _
            "v/d " & Mid(cell.Value, 5, 99)
        If Left(cell.Value, 4) =  "V.D " Then cell.Value = _
            "v.d " & Mid(cell.Value, 5, 99)
        If Left(cell.Value, 3) = "De " Then cell.Value = _
           "de " & Mid(cell.Value, 4, 99)
        If Left(cell.Value, 4) = "Van " Then cell.Value = _
           "van " & Mid(cell.Value, 5, 99)
        If Left(cell.Value, 4) = "Von " Then cell.Value = _
           "von " & Mid(cell.Value, 5, 99)
     Next   
     '-- some specific text changes to lowercase, not in first position
     rng.Replace what:= " a ", replacement:= " a ", _
       lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False
     rng.Replace what:= " and ", replacement:= " and ", _
       lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False
     rng.Replace what:= " at ", replacement:= " at ", _
       lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False
     rng.Replace what:= " for ", replacement:= " for ", _
       lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False
     rng.Replace what:= " from ", replacement:= " from ", _
       lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False
     rng.Replace what:= " in ", replacement:= " in ", _
       lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False
     rng.Replace what:= " of ", replacement:= " of ", _
       lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False
     rng.Replace what:= " on ", replacement:= " on ", _
       lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False
     rng.Replace what:= " the ", replacement:= " the ", _
       lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False
     '--- This is where you would code specific name changes
     '--- regardless of position of character string in the cell
     rng.Replace what:= "mcritchie ", replacement:= "McRitchie ", _
       lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False
     Application.Calculation = xlCalculationAutomatic
     Application.ScreenUpdating = True
     CapWords (mySelection)  'activate if you want to run macro
  End Sub

  Sub CapWords(Optional mySelection As String)
    'Expect all substitutions here would be to capitals
    'not necessarily limited to words
    Dim savCalc As Long, savScrnUD As Boolean
    savCalc = Application.Calculation
    savScrnUD = Application.ScreenUpdating
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Dim rng As Range
    On Error GoTo done   'In case no cells in selection
    If mySelection =  " " Then Set rng = Selection _
         Else: Set rng = Range(mySelection)
    rng.Replace What:= "IBM ", Replacement:= "IBM ", _
       LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
  done:
    Application.Calculation = savCalc
    Application.ScreenUpdating = savScrnUD
  End Sub
The Proper_Case macro had customizations that I wanted to use in other macros so I renamed it to Proper_Case_Inner and created another macro named Proper_Case that would still show up on the Macro List (Ctrl+F8). The Proper_case_inner macro accepts optional parameters like the range, which can only be specified in a macro called by another macro. The CapWords macro will not show up in list of macros (Alt+F8) but can be used directly or from another macro.
You can put it into a menu like I do, or you can assign a shortcut key to it of Ctrl+Shift+P so it doesn’t interfer with the Excel shortcut of Ctrl+p for Print. You can check for conflicts with preassigned shortcut keys by using your browser’s FIND (Ctrl+F) on the Shortcuts web page, where you will also find instructions for “Creating your own shortcut keys”.
Use StrConv instead of application.proper (#strconv)

Use Cell.Formula = StrConv(Cell.Formula,vbProperCase)
instead of Cell.Formula = application.proper(cell.formula)
Not only because the VBA is faster but because it won't change words like "doesn't" to "Doesn'T".

Some notations on the above code, and use of Special Cells (speed and efficiency considerations) « (#notations)
Normal use of this macro would be to run the macro by itself. There is an option parameter on the SUB that can use the range supplied by another macro, such as Invoking a Change Event macro to change to Proper Case on this web page.
Turning off Screen updating keeps the screen from refreshing with each cell change and improves performance.
Turning off Calculation even though you don’t see much in the way of calculations greatly improves performance. In XL95 use xlManual and xlAutomatic instead. (there is no numeric in these constants it is XL not X1).
DIM statement for each variable is recommended and is required with use of Option Explicit which in included at the top of modules in Excel 2000 by default. (VBE Tools, Editor, [x] Require Variable Definition). Be sure to have this option to help keep you out of serious trouble.
Testing for constants eliminates need to test for formula .. If cell.HasFormula = False Then ...
Reducing the cell selection to Text constants eliminates the need to check for a used cell range. Failure to make a reduction of one or the other would take forever if say you selected an entire column as there are a lot of rows in all versions of Excel.
There are 16,384 rows in XL95, and 65,536 in XL97 and up. In other words we do what we can to not check all 65,536 rows by 256 columns (16,777,984 cells), which might be what was selected.
Without the use of INTERSECT a single cell selection would default to the entire worksheet. But the INTERSECT keeps the selection at one cell (or less) when starting with a single cell selected as in just one active cell. This is a lot easier than using an IF to check the selection.count
Examples of Intersect and of Special Cells (also see warnings on use of Special Cells (more than 8,192 non-contiguous cells).
   Intersect(Selection, Selection.SpecialCells(xlConstants, xlTextValues))

   Intersect(Selection, ActiveSheet.UsedRange)

   Intersect(Selection, ActiveSheet.UsedRange, ActiveCell.EntireColumn)

   If Intersect(ActiveCell, Range("YellowRange")) Is Nothing Then
     MsgBox "Active cell is not in range of YellowRange =" & yellowrange.addresslocal(0,0)

   Dim rng As Range
   Set rng = Intersect(Selection, _
      Cells.Rows("2:" & Cells.Rows.Count), _
      Selection.SpecialCells(xlConstants, xlTextValues))
   If rng Is Nothing Then Exit Sub
   '-- Application.ScreenUpdating = False
XlSpecialCellsValue constants: xlErrors, xlLogical, xlNumbers, or xlTextValues. Also see xlcelltype constants on my formula page
SpecialCells(xlCellTypeFormulas, 23).Select 'All formulae
SpecialCells(xlCellTypeFormulas, 16).Select 'All formulae with errors
SpecialCells(xlCellTypeFormulas, 2).Select 'All formulae with text
SpecialCells(xlCellTypeFormulas, 4).Select 'All formulae with logic
SpecialCells(xlCellTypeFormulas, 6).Select 'All formulae with text or logic
This could result in no cells selected, so an error test in front
On Error Resume Next 'In case no cells in selection
You could be more specific using NOTHING in a test instead of coding for a general Error, but you still have to provide an Error check and will have to use even more coding.
  Dim rng As Range
  On Error Resume Next
  Set rng = Intersect(Selection, _
     Selection.SpecialCells(xlConstants, xlTextValues))
  On Error GoTo 0
  If Not rng Is Nothing Then
      For Each Cell In Intersect(Selection, _
          Selection.SpecialCells(xlConstants, xlTextValues))
                         ...
      next cell
  End If
End Sub
Simple macros to CLEAR selection of constants. For an example of clearing constants see InsertRowsAndFillFormulas Macro macro.
Sub ClearNumberConstants()
  On Error Resume Next
  Intersect(Selection, _
    Selection.SpecialCells(xlConstants, xlNumbers)).Clear
End Sub

Sub ClearConstants()
  On Error Resume Next
  Intersect(Selection, _
    Selection.SpecialCells(xlConstants)).Clear
End Sub
Find the first cell after the last constant in column A. Problem if you have more than 4096 separate areas of constants (see MSKP 832293 and notes below)
Sub afterconstants()
  Dim rng As Range, L As Long, M As Long
  Set rng = Columns("A:A").SpecialCells(xlCellTypeConstants, 23)
  L = rng.Areas.Count
  M = rng(L).Count
  rng.Areas(L).Item(M).Offset(1, 0).Select
End Sub
There are words that are normally not capitalized in titles and when using Proper Case. These are embedded words such as: a, and, at, for, from, in, of, on, and the. Also a fixup for my own name where ever it occurs in the selection. Many of these same words may be omitted from the beginning of a title for sorting purposes.
So what seems at first seems like very simple code should prevail actually requires more code than a simple example of something that works.
If your intent is to change people’s names that begin with “Mc”, “Mac”, etc. then I would suggest using a modified macro that specifically is to work with the names you will be working with, because the more code you add you will have to make manual changes later. “Mc” is a safe change in position 1, “Mac” would be less so, “De”, “Di”, “Le”, “van”, “von” etc will depend on who they are and how they really spell their own name. Mackey is not the same as MacKey, so it would come down to knowing your data, because you would have to make manual changes afterwards or provide for all exceptions in your code. See additional notes at the beginning of this Topic (Proper), as you will have to make manual changes no matter what you do, it is a matter of knowing your data and the people involved in your data.
For sorting purposes, in a different macro, you might want to eliminate specific article prefixes at the beginning of book titles, list of songs, library book catalog “A ”, “An”, and “The ”.
   If Left(Cell.Value, 2) = "A " Then Cell.Value = Mid(cell.value,3,999)
   If Left(Cell.Value, 3) = "An " Then Cell.Value = Mid(cell.value,4,999)
   If Left(Cell.Value, 4) = "The " Then Cell.value = Mid(cell.value,5,999)
A worksheet solution may also suffice, Rob Rosenfeld (excel.misc, 2005-11-06) offered the following solution:
    =IF(COUNTIF(ListOfWords,LEFT(A1,FIND(" ",A1)-1)), TRIM(MID(A1,FIND(" ",A1)+1,255)),A1)
where ListOfWords is a range where you have listed all of the first words which you want excluded. You may either NAME the range, or substitute the reference in the formula (e.g. Z1:Z10). Both of the above solutions use a helper column for the sort.
Example of Current Region involved when a single cell is selected
As previously described here is an example where a single cell is selected but the use of “Selection.SpecialCells(xlCellTypeConstants)” would cause cell involvement from the current region instead of a maximum of 1 cell.
  Sub DemoSingleCellSelect()
    Dim cell As Range
    Range("a1:k10") = "aa"   '-- try later with  = "=1+1"
    Range("a3:f4").Clear
    Range("a1").Select
    on error resume next    '-- in case no cells selected
    For Each cell In Selection.SpecialCells(xlCellTypeConstants)
       cell.Interior.colorindex = 38
   Next cell
  End Sub
Things to watch out for when using macros, your own or someone else’s

You want the macro to be as generic as possible: Look to see if you can use a selection of 1 cell, of a range, of multiple ranges, of the entire sheet. The inability to do each of these does not mean there is a problem with the macro, but the idea is to make things as generic as possible, and to know how it works. You normally do not want to make something production that addresses only specific cells.
If a macro addresses all cells in a selection you might want to reduce that considerably to prevent it from performing useless processing. Select the current region (Ctrl+Shift+*) or all cells in the used range (Ctrl+End to get last cell, then Ctrl+Shift+Home to extend back to first cell)
See Excel Shortcuts, some keys will not work as indicated if Transition Navigation is in effect.
Another way to limit usage is using Special Cells and limiting usage to constants, formulas, blanks (ISBLANK).

Evaluating various solutions including your own -- «Recap« (#recap)

Basically the solution should run fast, and should be able to
handle selection of an entire column easily. Selection of entire columns should not be noticeably slower than selecting only those cells in the column that have values,
limited processing within the used range
SpecialCells is one way of limiting to the used range. Must
not mess up formulas by converting them to values, and must
not reposition the view, and must
not have a flickering screen. It must
process only one cell when a single cell is selected instead of the Excel default to process the current region. Current region can be identified with CTRL+SHIFT+* (asterisk)
Multiple selections shoulr work -- Excel 2000 and up treat multiple selections as one selection if going cell by cell in the selection (for each cell in selection).

How much of a difference can use of SpecialCells really make (#speed)
My laptop speed is 600mHz. Here are some timings based on a macro you can find in my Slow Response page.
(New laptop in Aug 2007 is DELL Inspiron 1521 with 1.60 gigahertz AMD Turion 64 X2, so bad coding may be slightly less noticeable.)
The following took 366.066 seconds without reducing the selection to cells with actual data, in other words it processed every single cell in coumn E, there are only 22 rows even used. (Note that was when Excel had only 64,436 rows, Excel 2007 has over a million rows with new worksheets not in compatibility mode, so a faster machine with more memory won't cover up bad code)
  Dim i As Long
  For i = 1 To Cells.Rows.Count
    Cells(i, 5).Formula = _
    Application.WorksheetFunction.Proper(Cells(i, 5).Formula)
  Next i
Same thing using Special_cells took 0.040 seconds
   Dim cell As Range
   For Each cell In Columns("E").SpecialCells(xlConstants, xlTextValues)
        'cell.Formula = Application.Proper(cell.Formula) 'see strconv notes
        Cell.Formula = StrConv(Cell.Formula,vbProperCase)
   Next cell
invoking the proper_case macro took 0.070 seconds whether changes were actually made or not, involving excuting an external macro, and extra code for exceptions with some words after first word to appear in lowercase (and, the, on, a) to appear in lowercase,and other exceptions. The examples above just record the times for part of the coding shown.
   Columns("E").Select
   call  'personal.xls'!Proper_Case
Using Tom’s example calling macro installed in same workbook took 0.491 seconds, or if modified for only .Columns("e"). instead of .UsedRange. took 0.050 seconds, or 0.040 seconds if no updating changes needed. Note Screen Updating and Calculation were not turned off. With Screen Updating and Calculation turned off it took 0.030 seconds with or without changes actually being made. Had several screens of data been involved the differences would have been much greater.
   MakeProper
Example from Tom Ogilvy

Here is a solution from Tom Ogilvy 1999-02-07) that changes cells that are constants on the ENTIRE spreadsheet. The macro provided at the top will do the same if you Select ALL cells first before invoking the macro, but you might want to compare what is the same and what differs. One big difference will be the speed because it has not turned off calculation and screen updating, which would be very noticeable if there are a several screens of data involved.
   Sub MakeProper()
     Dim Cell as Range, rng1 as Range
     Set rng1 = ActiveSheet.UsedRange.SpecialCells(xlConstants, xlTextValues)
     For Each cell In rng1
        'cell.Formula = Application.Proper(cell.Formula) 'see strconv notes
        Cell.Formula = StrConv(Cell.Formula,vbProperCase)
     Next cell
   End Sub
For XL97 and above use xlCellTypeConstants instead of xlConstants

Additional considerations for speed and efficiency can be seen in Slow Response and Memory Problems, Speeding up Excel, Enhancing Performance
An anomaly from Dave Peterson (#anomaly)
The following works without looping individual cells, (2003-03-21), but requires processing of each area even in Excel 2000 and 2002 to prevent some #N/A! errors and grabbing data from the wrong areas either of which would destroy the data making the solution without using areas useless. Range test included in case the selection occurs entirely outside the used range.
  Sub MakeProper_Quick_test()
    Range("A1").Formula = "=""asdf ""&ADDRESS(ROW(),COLUMN(),4)&"" qwer"""
    Dim i As Long
    i = InputBox("type 1 to convert all to values", "values", 1)
    If i = 1 Then
        Cells.Copy
        Cells.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
    End If
    Range("A1").Select
    Selection.AutoFill Destination:=Range("A1:H1"), Type:=xlFillDefault
    Range("A1:H1").Select
    Selection.AutoFill Destination:=Range("A1:H29"), Type:=xlFillDefault
    Range("A1:H29").Select
    Range("B5:F17,H6:H12,D21:D25,G20:G26,B23:B27").Select
    Range("B23").Activate
    Application.Run "MakeProper_Quick"
  End Sub
 
  Sub MakeProper_Quick()
   'Dave Peterson, 2003-03-21, misc,  no loop required...
   '-- doesn't work with application.upper and application.lower
    Application.ScreenUpdating = False
    Dim myRng As Range
    Dim myArea As Range
    On Error Resume Next
    Set myRng = Intersect(Selection, ActiveSheet.UsedRange)
    If myRng Is Nothing Then
        MsgBox "Nothing in intersect range"
    Else
        For Each myArea In myRng.Areas
            myArea.Formula = Application.Proper(myArea.Formula)
            'myArea.Formula = Application.Proper(myArea.Formula) 'see strconv notes
        myArea.Formula = StrConv(myArea.Formula,vbProperCase)
        Next myArea
    End If
    Application.ScreenUpdating = True
  End Sub
Please see the thread. The usage results are not consistent unless one uses Areas but is still faster than going cell by cell.
Invoking a Change Event macro to change to Proper Case (#proper_change)
Change Event macros installed differently than regular macros. More information on Event Macros.
Either of the Change Event macros below can be installed with right-click on sheet tab, view code, plop code into module.
The following Change Event macro will invoke the Proper_case macro when a cell in Column D (col 4) is changed. The Proper_Case macro is the first macro shown in this article.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  If Target.Row  = 1 then Exit Sub 'don't override headings in row 1 
  If Target.Column <> 4 Then Exit Sub  'only allow changes to Col D 
  Application.EnableEvents = False
  Application.Run "personal.xls!Proper_Case", Target.Address
  Application.EnableEvents = True
End Sub
If you don’t want the additional features of the Proper_Case macro you can use the following code, but it will probably cause you more grief than help due to inability to override, not even by changing another macro.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  If Target.Column <> 4 Then Exit Sub
  'target.Formula = Application.Proper(target.Formula) 'see strconv notes
        target.Formula = StrConv(target.Formula,vbProperCase)
End Sub
Disadvantages of a Change Event Macro
– You cannot override the change even manually not even with copy and paste, except by deactivating the event macro.
– If you move the columns around, you will have to change the macro accordingly. To minimize damage to a column, you might check the row 1 value in the column before changing data in the column.

End of Material on Proper Capitalizations, beginning with the PROPER Worksheet Function.
This section also included introductory notes on the speeding up macros with the use of Intersect and SpecialCells.

Lowercase (#lower)

The worksheet function LOWER works the same as the PROPER worksheet but converts to lower case. function.
=LOWER(B2)
Example of a macro: (turns off screen updating and calculation in macro for Quicker results)
Sub Lower_Case()
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual          'in XL97
   Dim Cell as Range
   On Error Resume Next   'In case no cells in selection
   For Each Cell In Intersect(Selection, _
          Selection.SpecialCells(xlConstants, xlTextValues))
      Cell.Formula = LCase(Cell.Formula)
   Next
   Application.Calculation = xlCalculationAutomatic     'in XL97
   Application.ScreenUpdating = True
End Sub

Uppercase (#upper)

The worksheet function UPPER works the same as the PROPER worksheet function but converts to upper case.
=UPPER(B2)
I would have to say that except for zip state codes I have not much use for converting everything to uppercase.
Example of a macro: (turns off screen updating and calculation in macro for Quicker results)
Sub Upper_Case()
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   Dim Cell as Range
   On Error Resume Next   'In case no cells in selection
   For Each Cell In Intersect(Selection, _
          Selection.SpecialCells(xlConstants, xlTextValues))
      Cell.Formula = UCase(Cell.Formula)
   Next
   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True
End Sub
If appearance is all that matters there are some uppercase only fonts like Bank Gothic Mb BT, Copper Plate Gothic [Hv] BT (Cooperpot), Desdemona, Bassoon. You can find what you actually have by bringing the Unicode Character Map (shortcut) typing in your name in mixed case, and using the ArrowDN key to cycle through the fonts. Excel and other windows software does not provide access to codepages which could have been built to have upppercase only. Subset under font is as close as you will get to codepages. For an Excel solution see Copper Plate below.
Apply UpperCase selectively to some columns on a sheet (#ucase_event)
The following would change entries immediately as they are created using an Event macro. An Event Macro is apparently not effective until the workbook containing the macro is saved. Note event macros are stored with the worksheet unlike normal macros which are stored in modules.
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
It would be perhaps more common to only do this for say a zipstate code in something like column 6, where you might include
If Target.Column <> 6 Then Exit Sub
Back to Kindergarten (#kindy)
There is always the occassional request to change the text in all sheets to uppercase. As much as I dislike very many things in uppercase here is a solution.
Sub KindergartenFont()
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   Dim x As String, ws As Object, cell As Range
   x = MsgBox("Use CANCEL to abort changing all constant " _
       & "cells to uppercase", vbOKCancel)
   If x = vbCancel Then Exit Sub
   For Each ws In ActiveWorkbook.Sheets
    On Error Resume Next   'In case no cells in selection
    ws.Activate
    For Each cell In Cells.SpecialCells(xlConstants, xlTextValues)
        cell.Formula = UCase(cell.Formula)
    Next
  Next ws
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
End Sub

Special capitalization and formatting for surnames (#surname)

A B C D E F G

1 Phone Member Address City ST Zip+4 email

2 412-555-1212 BROWN, Mark 4210 Washington Rd Pittsburgh PA 15228-3000 mbrown@example.com

3 203-484-1234 TAYLOR 714 Old Post Rd North Branford CT 06471 ktaylor@example.com

4 505-924-1212 WATSON, James 240 E Main St Albuquerque NM 87103-8123 jim_watson@example.com

This is a specialized example to capitalize and bold the surname, you can find the complete macros for doing this at Surnames, Special formatting for LASTNAME, firstname, using mostly techniques described on this page (proper.htm). Coding makes use of code like this, which is actually limited to text constants.
      cell.Formula = UCase(cell.Formula)
      With cell.Font
        .FontStyle = "Bold"
      End With
      With cell.Characters(Start:=i + 1).Font
        .FontStyle = "Regular"
      End With
and because this has to be done for each cell there is a macro to change a selection of cells, and there is an Event macro to change anything entered as text constants into Column 2 (Column B).
Partial string lookups (#partialstring)

Looking up part of the cell. For example you have a name and address list and want to get to the K's quickly, or even more specifically to a lastname beginning with Ken. Another example:&nsp; if the cell contains “Dallas, Texas” it is to pick up the city of Dallas without regard to the comma and the remainder after the comma. See use of the FindFirstChar macro (code) described in posting (misc, 2002-10-31). Also see use of Partial String on VLOOKUP page.

Capitalize Cells with Formulas and/or Constants (#ucase_all)
The following will process all nonblank cells in the selection both formulas and constants, but will not waste time processing empty cells within the selection. Do keep in mind warnings about use of noncontiguous SpecialCells usage.
This is an example of use the of UNION to combine two different SpecialCells ranges, and then to process them in one loop.
Sub Upper_Case_ALL()
    'David McRitchie, programming, 2003-03-07
    Dim rng1 As Range, rng2 As Range, bigrange As Range
    Dim cell As Range
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    On Error Resume Next
    Set rng1 = Intersect(Selection, _
       Selection.SpecialCells(xlCellTypeConstants))
    Set rng2 = Intersect(Selection, _
       Selection.SpecialCells(xlCellTypeFormulas))
    On Error GoTo 0
    If rng1 Is Nothing Then
       Set bigrange = rng2
    ElseIf rng2 Is Nothing Then
       Set bigrange = rng1
    Else
       Set bigrange = Union(rng1, rng2)
    End If
    If bigrange Is Nothing Then
       MsgBox "All cells in range are EMPTY"
       GoTo done
    End If
    For Each cell In bigrange
       cell.Formula = UCase(cell.Formula)
    Next cell
done:
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub 
End of Material on Capitalized Cell Entries and Full Word capitalizations, beginning with UPPER Worksheet Function.

TrimSUB - trim cells in selection (#trimsub)
The TRIM Worksheet function removes spaces from beginning and end of text cells as well as extra internal spaces. Removes all spaces from text except for single spaces between words. The VBA equivalent only trims leading and trailing spaces. (turn off screen updating and calculation in macro for Quicker results). See the TrimALL macro for a more robust version with additional explanations and code you would actually want to use.
Sub TrimSUB()
   Dim icell as Range
   For Each icell In Intersect(Selection, _
         Selection.SpecialCells(xlConstants, xlTextValues))
      icell.Formula = Trim(icell.Formula)
   Next icell
End Sub
Insert Blank Rows (#insertblankrows)
For this example 2001-10-02, the poster requested that 2 blank rows be inserted for when the next cell in selected column is greater than the cell in the previous row of the column. Note use of INTERSECT.
Sub InsertBlankRows()
    Dim i As Long   'integer not sufficient
    Dim nRange As Range
    Set nRange = Intersect(Selection, ActiveSheet.UsedRange, _
       ActiveCell.EntireColumn)
    If nRange.Cells.Count < 2 Then Exit Sub
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    For i = nRange.Cells.Count To 2 Step -1
       If nRange.Item(i) > nRange.Item(i - 1) And _
            Not IsEmpty(nRange.Item(i - 1)) Then
            nRange.Item(i).Resize(2, 1).EntireRow.Insert
       End If
    Next
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub
The example was included here because of the use of INTERSECT and the manner of the loop, more comprehensive information on Inserting Rows.
Select cells matching activecell in the Selection area
Make new selections based on an original single (rectangular) selection based on the position of the active cell. In Excel 2000 you can use multiple selections with the Ctrl key which means the last selection, which can be a single cell, would be your active cell without any extra effort. (posted programming 2001-04-04).
With an initial selections of K8:Q15,K9:K9, would have K9 as the activecell
The resulting range of the example at right would be
Range("K9:K9,K10:K10,K11:K11,L11:L11,M11:M11,O11:O11," _
& "P11:P11,Q11:Q11,Q12:Q12,Q13:Q13,Q14:Q14,Q15:Q15").Select
Sub S_active()
  Dim cell As Range
  Dim i As Long
  Dim sStr As String
  For Each cell In Intersect(Selection, _
      Selection.SpecialCells(xlConstants, xlTextValues))
    i = i + 1
    If cell.Formula = ActiveCell.Formula Then _
       sStr = sStr & "," & cell.Address(0, 0) _
       & ":" & cell.Address(0, 0)
  Next cell
  Range(Mid(sStr, 2, Len(sStr) - 1)).Select
End Sub
K8 L8 M8 N8 O8 P8 Q8

K9 L9 M9 N9 O9 P9 Q9

K9 L10 M10 N10 O10 P10 Q10

K9 K9 K9 N11 K9 K9 K9

K12 L12 M12 N12 O12 P12 K9

K13 L13 M13 N13 O13 P13 K9

K14 L14 M14 N14 O14 P14 K9

K15 L15 M15 N15 O15 P15 K9
Additional Examples of Interesect and SpecialCellss (#intersect)
The following are somewhat offtopic for Proper Case etc., but since this page is also concerned with SpecialCells have included a few additional examples. (meant SpecialCells not SpecialValues)
Sub Formulas_to_Values()
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   Dim cell As Range
   On Error Resume Next   'In case no cells in selection
   For Each cell In Selection.SpecialCells(xlFormulas)
      cell.Value= cell.Formula
      If Trim(cell.Formula) = "" Then cell.Formula = ""
   Next cell
   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True
End Sub
Checking for Numeric Cells in a Worksheet Selection (#numeric)
The following may look a bit out of place on this page, but is included here because of use of celltypes ie. xlCellTypeFormulas, xlCellTypeConstants.
You can make similar macros named chkText and chkAllText to check for text instead by using xlTextValues instead of the 1 used in place of xlNumbers.
Sub chkAllNumeric()
    Cells.Select
    chkNumeric
End Sub
Sub chkNumeric()
    'David McRitchie in misc  2001-04-14  will be in proper.htm
    '   http://www.mvps.org/dmcritchie/excel/proper.htm
    'Something similar can be done with C.F.  =ISNUMBER(A1)
    Dim fNumeric As Range, cNumeric As Range
    On Error Resume Next
    Set fNumeric = Selection.SpecialCells(xlCellTypeFormulas, 1)
    Set cNumeric = Selection.SpecialCells(xlCellTypeConstants, 1)
    If fNumeric Is Nothing Then
       If cNumeric Is Nothing Then
          MsgBox "No numeric cells in selection"
       Else
          cNumeric.Select
       End If
    Else
       If cNumeric Is Nothing Then
          fNumeric.Select
       Else
          Union(fNumeric, cNumeric).Select
       End If
    End If
End Sub
Note that use of UNION requires each range to not be empty (not nothing), to obtain a UNION of several ranges omitting the empty ranges you might do better with a loop, see Tom Ogilvy’s posting 2002-12-30.
An unreliable visual indicator would be to look at the cell: if left justified text, if right justified numberic. This can be overridden by cell justification, so select the cell and make sure it is not specifically overridden with left/center/right justified.
To check a lot of cells would be to select all cells (Ctrl+A) then Edit menu, GoTo, [Special],
Constants & Number
Formulas & Number
Unfortunately you can’t do both at same time. Also note this is where you would choose "Blanks" to see if a cell is really Empty (no content, no formulas).
Use a Worksheet Function on an individual cell
=ISTEXT(A1)
=ISNUMBER(A1)
Use a Conditional Formatting with =ISNUMBER(A1) with one color and =ISTEXT(A1) with another color as described by Rob Bovey. Conditional Formatting will do no damage to your Excel File. To remove C.F. you will have to find C.F. usage which you can do by selecting a cell identified in the range and then Format --> C.F. as was used in creation.
Clear Number Constants (#clearnumberconstants)
Run the following macro to clear number constants from the selection area. The macro will leave text constants, formulas, and formatting alone.
Sub ClearNumberConstants() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim cell As Range On Error Resume Next 'In case no such cells in selection For Each cell In Intersect(Selection, _ Selection.SpecialCells(xlConstants, xlNumbers)) cell.Formula = "" Next cell Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub
Convert all cells to text values (#text)
Removes all formulas, converts all numbers to the text value, the text that you see displayed. Everything will be text when finished so will be left justified. If left justification is a problem see code for my Excel to HTML page. Useful for some tricky conversions to HTML or with use in Mail Merge. For your convenience this creates a copy of the active sheet so you don’t harm your original worksheet.
Sub AllCellsToText()
   'D.McRitchie, posted 2003-01-17 worksheet.functions
   'specifically for use with Mail Merge
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   Dim cell As Range
   Dim str  As String
   '-- to change the activesheet comment out the .copy line below
   Sheets(ActiveSheet.Name).Copy Before:=Sheets(ActiveSheet.Name)
   On Error Resume Next   'In case no such cells in selection
   For Each cell In Cells.SpecialCells(xlFormulas)
      cell.Value = cell.Value
   Next cell
   For Each cell In Cells.SpecialCells(xlConstants, xlNumbers)
     str = cell.Text
     cell.NumberFormat = "@"
     cell.Value = str
   Next cell
   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True
End Sub
For use with Mail Merge you want your text formatted sheet as the first worksheet in the workbook if using Excel 2000, so you should have the worksheet you want a text formatted copy of as the first sheet, or change the so it has =Sheets(1) instead of =sheets(ActiveSheet.Name the having the exc
AutoCorrect (#autocorrect)
There are some things that cause people problems with Auto Correct (Tools menu, Autocorrect)

Occasionally someone has a problem with getting a symbol instead of what they typed -- look for something in AutoCorrect.
Text entered as "Mr. and Mrs. Jackson" gets converted to "Mr. And Mrs. Jackson" this is because the period after Mr is recognized as an end of sentence. The solution is to use the Exceptions button on the AutoCorrect menu and add "Mr." into it. Everything there is an abbreviation ending with a period.
Text Comparison, Case Sensitivity (#compare)
In Excel most Worksheet Functions are case insensitive for comparisons so upper and lowercase letters compare equal. Exceptions include FIND, and SUBSTITUTE which are case sensitive. See strings page.
Worksheet Functions specific to changing case are: LOWER, PROPER and UPPER.
Worksheet Functions specific to exact comparison are: EXACT
An Example that would be useful in Conditional Formatting or to colorize entries in all uppercase or all lowercase or to prevent entries not in mixed case with in Data Validation. =IF(A1<>"",OR(EXACT(A1,LOWER(A1)),EXACT(A1,UPPER(A1))))
As an alternative you could use an Event macro to change such entries immediately to proper case when entered:
In VBA most comparisons are case sensitive and letter case makes a difference for comparisons. in a macro. If you want a comparison of text without regard to letter case in a macro, you can use
If ucase(var1) = ucase(var2) then ...
or faster using a text compare (1 or vbTextCompare)
If StrComp(var1, var2, 1) = 0 then ...' Returns 0 if values are textually equal.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   Cancel = True   'Get out of edit mode 
   Range("A1").Value = "Banana"
   MsgBox InStr(1, Range("A1").Value, "banana") > 0  'case sensitive
   MsgBox InStr(1, Range("A1").Value, "banana", vbTextCompare) > 0  'insensitive
   MsgBox Range("A1").Value Like "*banana*"     'case sensitive
   MsgBox LCase(Range("A1").Value) Like "*banana*"  'case insensitive
End Sub
VBA Functions specific to changing case are: LCASE and UCASE, for Proper you would have to use Application.Proper in your code to use the Excel function. But better would be Cell.Formula = StrConv(Cell.Formula,vbProperCase) -- See strconv notes
More on Like and more information on strings.
Convoluted Methods (#convoluted)

Suggestions are sometimes made to use MS Word to convert the lettercase in Excel. See thread. With a macro that uses SpecialCells you can select entire columns and only process cells with text content. Processing empty cells in a macro or otherwise would take a very long time.

Some notes or postings on Failure to use Special Cells (#failure)

Macro attempts to change thousands representations into actual values, but doesn’t use specialcells to reduce selection to number constants only -- trailing zeros.
Also see warnings note on failure of SpecialCells when too many areas.
Turning off Events (#problems)
Just in case you somehow manage to abend (terminate) a macro that turned of EnableEvents you will not be able to run any Event macros until you reenable events. You will have to run a macro from a standard code module.
Sub Fix_Things()
   Application.DisplayAlerts = True 
   Application.EnableEvents = True    
End Sub
A more complete macro can be seen in the reset_things macro in
http://www.mvps.org/dmcritchie/excel/code/proper.txt
Related Items (#related)

How to install/use a macro can be found on my formula page and in Getting Started with macros as noted at the top of this page and most of my pages with macros.
HELP --> StrConv [vbUpperCase, vbLowerCase, vbProperCase]
= StrConv(Cell,vbLowerCase) page.

Related Information on My Excel Pages:

Rearranging Data in Columns -- Rearrange columns by splitting, joining, or reversing columns of data. Join() can be used as a reversal of a correctly executed text to columns. Lastname() can be used as a put lastname first is not already done. SepTerm() can be used as a more limited version of text to columns that only separates from the first word. SepLastTerm() separates last word from the beginning of text. ReversI() is used to reverse the order of items in a row, column, or range. MarkCells() is used to create test data within the invoked range. Also includes use of fill handle and creating a sheet with short cut keys for reference.
Slow Response in Excel and the means of speeding up some things.

To quickly access the Proper_Case, Upper_Case, and Lower_Case macros on this page, I have them on a Menu. Example of my menus can be seen in the output from the barhopper macro on my page. To assign a macro to a menu, toolbar see Toolbars page. If you assign a shortcut key you might want to check that with shortcut keys preassigned in Excel.

Related Information in Google Usenet Archives

Re: Change Case a reply posting by Bill Manville. Bill’s code will also handle cells with formulas by enclosing the formula rather than simply changing data. Actually in most cases changing the input will have already done this.

Re: Leading capital a reply posting by Harold Staff. Harold’s code to capitalize only the first letter of the first word in a cell.

Re: Forcing Capitalization a reply posting by Chip Pearson applicable to XL97 using a Private Worksheet_Change event procedure.

SMALLCAPS, by PAT FINEGAN, 2000-06-26, misc. Looks better in bold SMALLCAPS, by PAT FINEGAN. The Copper Plate Gothic fonts are the only fonts that came with Excel that do this on their own.
Sentence_Case, Re: Use VBS RegExp to replace a-z with A-Z?, Tushar Mehta, programming, 2002-08-04. Also read about Regular Expressions to understand Tushar’s macro.

Microsoft Knowledge Data Base (MS KB)

Q107564 XL: Not All Worksheet Functions Supported as Application (in VBA)

Run-Time Error '438':

Object doesn’t support this property or method

Other places (#other)

Disable “Caps Lock” [LG]. I physically remove Caps Lock key except on a laptop where I use a barrier. for better solutions see Caps Lock on my shortcut keys page.

You are one of many distinguished visitors who have visited my site here or in a previous location since July 17, 1998. [My Excel Pages -- home] [INDEX to my site and the off-site pages I reference]
[Site Search -- Excel] [Go Back] [Return to TOP]
Please send your comments concerning this web page to: David McRitchie send email comments
Copyright © 1997 - 2010, F. David McRitchie, All Rights Reserved

	A	B	C	D	E	F	G
1	Phone	Member	Address	City	ST	Zip+4	email
2	412-555-1212	BROWN, Mark	4210 Washington Rd	Pittsburgh	PA	15228-3000	mbrown@example.com
3	203-484-1234	TAYLOR	714 Old Post Rd	North Branford	CT	06471	ktaylor@example.com
4	505-924-1212	WATSON, James	240 E Main St	Albuquerque	NM	87103-8123	jim_watson@example.com