Excel.Tips.Net ExcelTips (Menu Interface)

Forcing Input to Uppercase

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Forcing Input to Uppercase.

If you are developing a worksheet for others to use, you may want them to always enter information in uppercase. Excel provides a worksheet function that allows you to convert information to uppercase, but it doesn't apply as people are actually entering information. For instance, if someone enters information in cell B6, then the worksheet function can't be used for converting the information in B6 to uppercase.

Instead, you must use a macro to do your changing for you. When programming in VBA, you can force Excel to run a particular macro whenever anything is changed in a worksheet cell. The following macro can be used to convert all worksheet input to uppercase:

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
    If Not .HasFormula Then
        .Value = UCase(.Value)
    End If
End With
End Sub

For the macro to work, however, it must be entered in a specific place. Follow these steps to place the macro:

  1. Display the VBA Editor by pressing Alt+F11.
  2. In the Project window, at the left side of the Editor, double-click on the name of the worksheet you are using. (You may need to first open the VBAProject folder, and then open the Microsoft Excel Objects folder under it.)
  3. In the code window for the worksheet, paste the above macro.
  4. Close the VBA Editor.

Now anything (except formulas) that are entered into any cell of the worksheet will be automatically converted to uppercase. If you don't want everything converted, but only cells in a particular area of the worksheet, you can modify the macro slightly:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Application.Intersect(Target, Range("A1:B10")) Is Nothing) Then
    With Target
        If Not .HasFormula Then
            .Value = UCase(.Value)
        End If
    End With
End If
End Sub

In this particular example, only text entered in cells A1:B10 will be converted; everything else will be left as entered. If you need to have a different range converted, specify that range in the second line of the macro.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2568) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Forcing Input to Uppercase.

Related Tips:

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!


Leave your own comment:

  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*What is 5+3 (To prevent automated submissions and spam.)
           Commenting Terms

Comments for this tip:

Willy Vanhaelen    08 Nov 2016, 12:42

This version of the macro should do what you want:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.HasFormula Then Exit Sub
   Application.EnableEvents = False
   Target = Evaluate(Replace("IF(ROW(*),UPPER(*))", "*", Target.Address))
   Application.EnableEvents = True
End Sub

It is not the macro code who disables the UNDO stack but it is "hard-coded" in EXCEL who clears the undo stack whenever any macro is run.
VAL    07 Nov 2016, 20:35
Hi Willy,
I wanted to let you know about a bug with your code: when you a copy/paste of multiple cells the values of all the cells pasted are set equal to the first cell in the copied range. Also the code disables the UNDO command for the edited cells.

Willy Vanhaelen    07 Feb 2016, 12:53
The macros in this tip have a bug and are even dangerous to use !!!

When you enter text the macro is fired, changes the text to upper case and re-enters it in the worksheet which fires the macro... Need I say more?

So to avoid this

   .Value = UCase(.Value)

must be replaced by (see the 2007 version)

   Application.EnableEvents = False
   .Value = UCase(.Value)
   Application.EnableEvents = True

Now the bug:

The macros of this tip crash when you select more than one cell, enter text and press Ctrl+Enter.

This version of the first macro deals with it:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.HasFormula Then Exit Sub
   Application.EnableEvents = False
   Target = UCase(Target.Cells(1))
   Application.EnableEvents = True
End Sub

When you enter data in a multiple cell selection and press Ctrl+Enter, Excel enters the data in all cells of the selection and then fires Worksheet_Change. Target does not refer to the active cell but to the entire selection range. So Target.Value = UCase(Target.Value) crashes because UCase can only change text in a single cell. Target = UCase(Target.Cells(1)) in my macro works because Target.Cells(1) is a single cell and Excel fills the entire Target range with its result.

BTW: there is a quicker way to go to the code window of the worksheet: simply right click the sheet's tab and select View Code.
Dan Fynn    06 Feb 2016, 21:46
Very useful but for those familiar with how to write micros. Pls how do we get the micros for beginners?

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us


Advertise with Us

Our Privacy Policy

Our Sites


Beauty and Style




DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)



Home Improvement

Money and Finances


Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives


Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2017 Sharon Parq Associates, Inc.