Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. 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

Written by Allen Wyatt (last updated April 27, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


61

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 the 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)
    If Target.HasFormula Then Exit Sub
        Application.EnableEvents = False
        Target = UCase(Target.Cells(1))
    Application.EnableEvents = True
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.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9813) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. 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

Understanding Views

Want to see how your document will look before it's printed? Or, do you want to see what things will look like if you put ...

Discover More

Tracing Errors

Sometimes it can be confusing to figure out the source of an error that is displayed in your worksheet. Excel provides a ...

Discover More

Changing the Axis Scale

When creating a chart, you may want to adjust the default scaling that Excel applies to an axis. This is relatively easy ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Ignoring Paragraph Marks when Pasting

Paste information in a worksheet, and you may end up with Excel placing it into lots of different cells. If you want it ...

Discover More

Copying Rows between Worksheets Based on a Text Value

Want to move data from one worksheet to another based on a text value in a column. There are a couple of ways you can ...

Discover More

Splitting Sentences to Cells

If you have a lot of text in your workbook, at some point you might want to split out sentences into individual cells. ...

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}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. 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 four less than 6?

2022-12-31 02:29:34

Anjali Rana

Amazing! It Works...

Thanks


2021-08-05 05:54:15

Willy Vanhaelen

@Peter Atherton
You are right, people not often read the all the posts.

@Peter Atherton and Shivakumar
There is no post dated 28 09 2018. The correct date is 9 Nov 2018 (the first of two) :-)


2021-08-04 04:01:22

Peter Atherton

Shivakumar

It is always good to read all the posts - see Willy Vanhaelen's reply dated 28 09 2018


2021-08-03 13:04:13

Shivakukmar

Hi,

I have a workbook that contains 200 Sheets and I want a range of Cells in all these sheets to auto convert text to capitals.

How do I paste this codes that can be applied for the entire workbook.

Please help.

Shivakumar


2020-10-19 09:56:53

Allen

Tony, the .HasFormula property is checked because it is assumed you don't want to convert formulas.

-Allen


2020-10-19 09:42:05

Tony

PS: I used Willy V's compact code but even formulas get re-capitalized.


2020-10-19 09:33:38

Tony

This is a really handy tool.
Can you elaborate on why the .HasFormula check is included?


2019-04-04 10:33:48

Michael Maus

Thanks Willy for sticking with this thread. Your first post from 2014 helped me when the above code would error out and break the whole sheet if I selects multiple cells and deleted the values from them. Your code posted on 10-19-14 cleared it the issue up quit nicely.

Thank you.


2019-03-13 11:47:00

Phil Reinemann

I'm surprised Excel doesn't have a uppercase cell text style, or like Word, an all upper case option for the style (well, at least for Times New Roman that option exists).


2018-11-09 11:24:26

Willy Vanhaelen

@David
This macro works even if you copy data from another workbook into this one:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.EnableEvents = False
Target = Evaluate("IF({1},UPPER(" & Target.Address & "))")
Application.EnableEvents = True
End Sub

Note that formulas are pasted as values.


2018-11-09 10:39:40

Willy Vanhaelen

@David
Yes you can. Place the macro in ThisWorkbook but you have to change its name:

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


2018-11-08 13:27:19

David

@ Willy Vanhaelen

Your code at the bottom, posted 10/19/14, works great! Can I use this same code and paste it in the workbook on the VBA editor? Or do I have to go to all the individual sheets and paste the code to apply to entire workbook?

Thanks for your help,
David


2018-02-08 20:08:43

Andreas Boozon

This is Excel-lent! :)

Special thanks to Willy Vanhaelen for ironing out the wrinkles!


2018-01-13 05:34:30

Willy Vanhaelen

@Sam
I still work with Excel 2007. VBA code is highy compatible with all versions of Excel 2007 and up.


2018-01-12 08:00:27

Sam

@Willy Vanhaelen Thank you very much for that information, I saw your previous post but was not sure how to add multiple ranges of cells to your code. Also as far as the ranges of cells and long code, I thought that if I did as you did that it would roll over once it was done with B5 on to row 1 of the next column, I was wrong. Do you know where I can find code that works better for Excel 2016, I have noticed that a lot of the codes on this site have issues similar to the code by the original author of this post. Thanks again! Sam


2018-01-12 07:27:36

Willy Vanhaelen

@Sam
BTW: [F2] is a shortcut for Range("F2")


2018-01-11 11:54:25

Willy Vanhaelen

@Sam
Your macro can be drastically reduced to:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.HasFormula Then Exit Sub
If Not Intersect(Target, Union([F2], [I2], [L2], [B4:F9])) Is Nothing Then
Application.EnableEvents = False
Target.Value = UCase(Target.Cells(1).Value)
End If
Application.EnableEvents = True
End Sub

This macro doesn't generate an error if you delete multiple X's at once. See my comment of 2014-10-19 for the expanation.


2018-01-10 21:35:29

Sam

This is fantastic and exactly what I am looking for. I am creating a workbook to track map completion in a game. The only problem I am having now is that once I put the code in I added multiple IF statements for different ranges of cells and it works great until I highlight multiple cells in any range and click DELETE to remove the X's I put in it will go into debug mode and then the code stops working. I just want in some of the cells in each workbook to accept a capitol X only even if someone types in a lower case X. below is the code I am using. Is there a better way to do this or did I do it wrong when I added multiple IF statements? Thanks! (I am using Excel 2016).

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Application.Intersect(Target, Range("F2")) _
Is Nothing) Then
With Target
If Not .HasFormula Then
Application.EnableEvents = False
.Value = UCase(.Value)
Application.EnableEvents = True
End If
End With
End If
If Not (Application.Intersect(Target, Range("I2")) _
Is Nothing) Then
With Target
If Not .HasFormula Then
Application.EnableEvents = False
.Value = UCase(.Value)
Application.EnableEvents = True
End If
End With
End If
If Not (Application.Intersect(Target, Range("L2")) _
Is Nothing) Then
With Target
If Not .HasFormula Then
Application.EnableEvents = False
.Value = UCase(.Value)
Application.EnableEvents = True
End If
End With
End If
If Not (Application.Intersect(Target, Range("B4:B9")) _
Is Nothing) Then
With Target
If Not .HasFormula Then
Application.EnableEvents = False
.Value = UCase(.Value)
Application.EnableEvents = True
End If
End With
End If
If Not (Application.Intersect(Target, Range("C4:C9")) _
Is Nothing) Then
With Target
If Not .HasFormula Then
Application.EnableEvents = False
.Value = UCase(.Value)
Application.EnableEvents = True
End If
End With
End If
If Not (Application.Intersect(Target, Range("D4:D9")) _
Is Nothing) Then
With Target
If Not .HasFormula Then
Application.EnableEvents = False
.Value = UCase(.Value)
Application.EnableEvents = True
End If
End With
End If
If Not (Application.Intersect(Target, Range("E4:E9")) _
Is Nothing) Then
With Target
If Not .HasFormula Then
Application.EnableEvents = False
.Value = UCase(.Value)
Application.EnableEvents = True
End If
End With
End If
If Not (Application.Intersect(Target, Range("F4:F9")) _
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


2017-09-20 23:31:41

Christopher

Hello,

I used your VBA code to force capital letters in a specific excel cell. It worked fine, so thank you very much :)

I only have one question: I want this cell to have a TEXT written in it "by default". For example "SUPPLIER'S NAME". When someone writes "COCA COLA" then it is OK. When some writes "Coca cola" then it is forced (by the vba code) to capital letters, so again fine. But when someone deletes everything, I would like the cell to write "SUPPLIER'S NAME".

Is that possible?

I would highly appreciate if u could reply to my email address, as I am not much of an internet person and I rarely manage to follow up blogs etc, if at all. Thank you so much for the help.

Christopher


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.