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

Forcing Input to Uppercase

by Allen Wyatt
(last updated October 11, 2014)

42

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
            Application.EnableEvents = False
            .Value = UCase(.Value)
            Application.EnableEvents = True
        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
                Application.EnableEvents = False
                .Value = UCase(.Value)
                Application.EnableEvents = True
            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 (9813) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Forcing Input to Uppercase.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Displaying a Message in the Status Bar

A great place for your macro to display status information is, well, in the status bar. Displaying the information is easy, ...

Discover More

Finding Alternate Words

How to expand your vocabulary by using the Thesaurus.

Discover More

Unwanted Cover Pages with Print Jobs

When you print a document, do you get more than you bargained for? If you get extra pages printed either before or within ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Can't Empty the Clipboard

The Clipboard is essential to move or copy information from one place in Excel to another. If you get an error when you try ...

Discover More

Deleting Duplicate Columns

Got a worksheet in which there may be entire columns that are duplicates of each other? If you want to delete those duplicate ...

Discover More

Entering Numbers in Excel

Enter information into a cell, and Excel needs to figure out what type of information it is. Here's how Excel interprets ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is nine minus 5?

2017-05-14 01:41:51

Mark

When you add one of these VBAs to an Excel file, in order to save the file, do you have to change the suffix to .XLSM (I am using Excel 2016)?


2017-03-30 11:28:09

Willy Vanhaelen

@Brian
This is a bug of this tip's macro. It happens only if you delete more than one cell at once (or when you enter data in a multiple cell selection and press Ctrl+Enter). I mentioned this already in my comment of 19 October 2014 (at the bottom of the list). If you use my version of the macro you will not have this error.


2017-03-29 13:11:43

Brian

Thanks for your help...

I am getting an error... mismatch, which the ****.Value = UCase(.Value)***** code is highlited.

please help, by the way this happens after i try to delete the text...

Brian


2017-03-02 16:51:02

Roy Cantel

I, too, have had the same issues as Mr. Webberking.

I am saving my file as a PASSWORD PROTECTED, Excel Macro-Enabled Template Form, where only (at this point in time) THREE cells require change to UPPERCASE font. (And, NO, these cells are NOT Locked.) Ideally, I'd also like to have this file opened up via a Shortcut (more issues, there, too.) I've even lowered all Security settings regarding Macros. No luck.

Running this only as a "straight" Excel Worksheet, I saw this VBA script work only ONCE.
Since this incident, I have never been able to replicate its success.


2017-01-03 15:53:08

Fred Webberking

Dear Allen,

I used your first mention of your VBA code to force cells in Excel to change for lower case to upper if the operator enters a lower case input, but I found this only worked while I was actively working in the worksheet. When I saved and closed the workbook and later re-opened it the VBA code didn't work. Is there a way to save the workbook so that the VBA code will work on subsequent closing and re-opening of same workbook on the same worksheet each time. Thank you.

Fred.


2016-12-10 00:26:05

Peter Gibson

Thanks for your handy tip regarding forcing text to be uppercase in Excel. Very helpful.


2016-09-07 05:04:13

Craig

Thanks for the macro.

Instructions easy to follow and does what it says it will.


2016-08-30 13:27:22

Paul Shepherd

Thanks Willy! Perfect :-)
Works like a charm on MAC and PC.

Thanks,
Paul


2016-08-30 05:44:00

Willy Vanhaelen

@LukeB

This is because de capitalisation is done by a macro and running a macro clears the undo stack. That is inherent with Excel.

Besides, even if your action could be undone, the macro would immediatly capitalise it again. That where it is designed for :-)


2016-08-30 02:46:44

LukeB

hi all, silly question, but when I implement this then I cannot do an undo. For example I type into a cell 'ab' and it gets changed to 'AB' which is great, but if I press undo on menu or cntl+X it does not undo ...


2016-07-30 08:09:17

Willy Vanhaelen

@Paul Shepherd

Try this one:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.HasFormula Then Exit Sub
If Not (Application.Intersect(Target, Range("C5:C279")) Is Nothing) Then
Application.EnableEvents = False
Target = Evaluate(Replace("IF(@="""","""",LOWER(@))", "@", Target.Address))
Application.EnableEvents = True
End If
End Sub

Let me know if it works.


2016-07-29 19:43:05

Paul Shepherd

I should add that I am using a clear form button so .Value = UCase(.Value) crashes on me with a runtime error 13, Type mismatch. Target = LCase(Target.Cells(1)) prevented the error.


2016-07-29 19:36:51

Paul Shepherd

Hi Willy!
thanks for this thread, it's been awesome and helped me build auto changing of uppercase to lowercase into my Excel sheets. One thing I have run into is that the buyers who use the form I made, copy and paste multiple sku#s from one sheet into a the column of another.
When they paste, these alphanumeric "numbers" into the column with this code, only the top entry gets repeated in all cells.

Any ideas? If I missed it below, I appologize!

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.HasFormula Then Exit Sub

If Not (Application.Intersect(Target, Range("C5:C279")) Is Nothing) Then
With Target
Application.EnableEvents = False
Target = LCase(Target.Cells(1))
Application.EnableEvents = True
End With
End If

End Sub


2016-07-02 15:59:28

Anton Jansen

Willy, thank you it solved the problem


2016-07-01 14:10:13

Willy Vanhaelen

@Anton

I mentioned this problem already in my comment of 19 Oct 2014.

If you replace
.Value = UCase(.Value)
with
.Value = UCase(.Cells(1))
this error will not occur.

For an expanation why: see my comment of 19 Oct 2014.


2016-06-29 15:56:03

Anton

Hi Allen,

I have used the second routine as I wanted to use a range only. This works very well, thank you. Just one problem, when I select e.g. cells B3:B7 and delete the contents using the Delete key on the keyboard, the code give an error and the routine no longer works. Can you help?

Thank you


2016-04-28 02:52:39

Amit

Hi All,

I am trying to force user to enter data in Upper case only but my file type has to be a Template. It is not letting me save the code in developer window.

Please help.


2016-03-03 05:09:54

Jingo

Thanks for This. It works great.

Question is how do I add another rule applying another set of range to Proper (I got the formula already) but I dont know how to add another rule.

Thanks


2015-11-26 12:30:54

Willy Vanhaelen

@Stephen T.

This will do: .Value = StrConv(Target(1),vbProperCase)

@Cholfer
.Value = UCase(Left(.Value, 1)) & Right(.Value, Len(.Value) - 1)
results in sentence case: only the first word is capitalised.


2015-11-25 10:47:10

Cholfer

One more thought. You could use a "hidden" cell for your input and use the proper() function in the cell in which you want the capitals.

I understand that you can use:
Application.WorksheetFunction.Proper(oCell.Text)

where oCell.Text is the cell address....Function taken from here

http://www.xcelfiles.com/VBA_Quick16.html

but I haven't tried it.


2015-11-25 07:35:30

Cholfer

I should have added that this only changes the first letter of your string. If you intend to have only two words then you could modify the line to include searches for spaces and make the letter after the space upper case but to do more would probably be impossible unless you could guarantee the length of the words in your string.


2015-11-25 07:31:57

Cholfer

Changing the .value line to:
.Value = UCase(Left(.Value, 1)) & Right(.Value, Len(.Value) - 1)

seems to work.


2015-11-24 18:06:36

Stephen T.

How do you change it to only capitalize the first letter of each word? (Ex.: this word --> This Word)


2015-08-30 11:35:13

kassim

Thank you for this it works for excel,
can you provide on that works for the google sheets because when i upload my sheet with this macro,the caps-lock is disabled


2015-06-30 12:45:44

SDECKER

I have tried most of what has been said above and have had errors or cells that would not change. I kept on looking and finnaly found a formula that worked. This is by Peter_sss on MrExcel on May 19th 2011 - http://www.mrexcel.com/forum/members/peter_sss.html

[Private Sub Worksheet_Change(ByVal Target As Range)
Dim changed As Range, c As Range
Dim cVal

Const myR As String = "B5:BK16,D3,G20:H22" '<- Your range(s)

Set changed = Intersect(Target, Range(myR))
If Not changed Is Nothing Then
Application.EnableEvents = False
For Each c In changed
cVal = c.Value
Select Case True
Case IsEmpty(cVal), IsNumeric(cVal), _
IsDate(cVal), IsError(cVal)
' Do nothing
Case Else
c.Value = UCase(cVal)
End Select
Next c
Application.EnableEvents = True
End If
End Sub ]

I tried this and this worked perfectly. i just had to share this since post above were not working.


2015-06-12 06:04:23

Cholfer

Eugene, if you just try it you will find that sheet 2 will pick up the case created in sheet 1


2015-06-11 12:39:16

Eugene

Allen you're a star.
Just a question and assistance.
I've copied the second macro in the VBA Editor and it works when you type a lower case text into that specific data range.
But what if I am using 2 or more sheets and I have a =(Sheet1!A2) in Sheet2 that draws text from the first Sheet into A2 in Sheet2.
Hope that makes sense.


2015-06-01 09:20:03

Glenn Case

Mort:

Genreally, you set Application.EnableEvents to False in order to prevent recursive calling of a procedure. For a WorkSheet_Change event macro, if you are changing the contents of a cell within the macro, then that change within the macro will trigger a new calling of the macro. Using Application.EnableEvents = False stops the triggering of any Event codes until it's reset to True.


2015-05-31 17:16:38

Mort Wakeland

Update - it works - I had commented out the Application.EnableEvents -

I am not sure where to places these and where not to place there, obviously you and Allen know - I have not clue - fully understand about infinite loops for my old Fortran days.
The Range "B12:B12" works!! When I enter a y turns to Y or n turns to N.
Feel sooo much better now. If advice on learning where to use EnableEvents = False/True would appreciate. Mort


2015-05-31 16:47:40

Mort Wakeland

Glenn/Allen

Quick update - ya'll are astute!
?Application.EnableEvents in Immediate window showed False just as you predicted - I wonder why - I have scattered through code on this one worksheet - EnableEvents = False, but I'm sure I turn them back on with
EnableEvents = True on that section of code. Will add true to the immediate window and see if Allen's code works.
Nope - doesn't work show this line of code highlighted in yellow if I click "debug"
If Not (Application.Intersect(Target, Range("B12:B12")) Is Nothing) Then
However, oddly, when I go back to the worksheet the y I entered into cell B12 is now Y. My head hurts - smile. Thoughts? Mort


2015-05-31 16:36:24

Mort Wakeland

Glenn

Most appreciate the response. I'll have to refresh my memory banks on the Immediate Window...am not a programmer, but do use Excel quite a bit. I think you are right....What's buggin me now is that I used Allen's great code to force an entry just in cell B12 to Upper and it's not working - I've looked all day on it, maybe just maybe it is the application issue...??? I used the range as ("B12:B12") or should I simply have used Range ("B12"). My memory is getting a lil shaky at 67 - even with the Ginko - smile. Happy Sunday. Mort


2015-05-26 09:08:29

Glenn Case

Mort:

I suspect that somehow your system has Application.EnableEvents set to False. This can happen, for instance, if your macro were to encounter an error and end prematurely. If this is the case, then your event macros will not trigger any more. You can check this by entering ?Application.EnableEvernts in the Immediate window of the VBA editor. It will return either True or False to indicate the condition. If it is False, then that's likely your issue. To fix it, you can enter Application.EnableEvents = True into the immediate window. To address this, I generally try to trap errors and run a routine to reset anything such as this before the macro ends.

Another possible issue, especially if you use very large workbooks or other files and/or leave Windows running for long periods of time, is that Windows programs such as Excel can begine to exhibit unpredictable behavior as available memory becomes limited; shutting down Windows and restarting generally fixes those issues.

Hope this helps.


2015-05-24 11:11:54

Mort Wakeland

Allen!
I first of all applaud you on your ability to communicate effectively. I can clearly understand what you write! On the other hand, some folk's should enroll in 3rd grade English!

What sends me off the "deep end" is code that will work one day and not work the next! There is some subtle something I'm not getting.

I have an Excel Table, and within the range of [headername1]:[headername2] I want to center and capitalize the entry if not already capitalized. My code is:

If Not Application.Intersect(Target, Range("DataEntry[[FormType(R/A)]:[BasePenalty]]")) Is Nothing Then
With Target
If Not .HasFormula Then
Application.EnableEvents = False
.Value = UCase(.Value)
.HorizontalAlignment = xlCenter
Application.EnableEvents = True
End If
End With
End If

Note the above code is within:
Private Sub Worksheet_Change(ByVal Target As Range)

Last week it worked, this week it is not working and I cannot for the life of me figure out why...I'm all ears and eyes to suggestions, observations or comments.
Best Memorial Day Weekend!
Mort Wakeland
(Dallas, TX)


2015-05-12 06:22:05

Willy Vanhaelen

@Jeremy
I gave the answer and a solution already on 19 Oct 2014. See below.


2015-05-12 02:10:36

Jeremy

Why do I get Runtime error 13, Type Mismatch, when I clear data from multiple cells at one time?


2015-04-18 11:36:17

Cholfer

I used the pre 2007 version but it fails when I try to delete the contents of the cell. Changing "If Not .HasFormula Then" to "IF Not .HasFormula Or Length <> 0 Then" overcomes that problem but when the specified cell is part of a line of deletions it fails with Run-Time error '13' type mismatch. Any ideas?


2015-01-20 18:06:35

DawnTreader

Rats, amend the code to:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.HasFormula Then Exit Sub

If Not (Application.Intersect(Target, Range("DataList[@[Unit_To]]")) Is Nothing) Then
With Target
Application.EnableEvents = False
.Value = UCase(.Value)
Application.EnableEvents = True
End With
End If

End Sub

I put a range around target to try and fix the problem. still errors on it the same way.


2015-01-20 18:03:42

DawnTreader

I am trying this on a set of cells in a table range. this code is not working:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.HasFormula Then Exit Sub

If Not (Application.Intersect(Range(Target), Range("DataList[@[Unit_To]]")) Is Nothing) Then
With Target
Application.EnableEvents = False
.Value = UCase(.Value)
Application.EnableEvents = True
End With
End If

End Sub

I get a runtime error 1004, method 'Range' of object '_Worksheet' failed on the "if not (application" line.

it shows that target is the value of the cell not the address of the cell when I hover over and get the tooltip.

what needs to change?


2014-12-02 09:37:22

Willy Vanhaelen

@ Glenn Case. Inadvertently entering data in a multi-cell selection is not likely to happen easily because you also have to hold down Ctrl while pressing Enter. When you simply press Enter the data is only entered in the active cell which is probably what you want.


2014-12-01 08:54:07

Glenn Case

Hey, Willy:

That's a neat trick! I had been typically screening to ensure the selection included only one cell, but there are legit instances when you want to enter into several cells. The only issue remaining is that I sometimes inadvertantly select more than one cell when I only want one, and with a macro, you lose Undo. That could be addressed with either a pre-change messagebox asking for confirmation or a post-change offer to change back. Either way, I apprciate the method to accept a multiple-cell selection. Thanks for the tip.


2014-11-26 00:23:10

ateendra

thanks!! really worked like charm


2014-10-19 12:56:12

Willy Vanhaelen

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.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.