Forcing Input to Uppercase

Amazing! It Works...

Thanks

@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) :-)

Shivakumar

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

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

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

-Allen

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

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

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.

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).

@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.

@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

@ 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

This is Excel-lent! :)

Special thanks to Willy Vanhaelen for ironing out the wrinkles!

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

@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

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

@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.

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

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

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)?

@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.

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

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.

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.

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

Thanks for the macro.

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

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

Thanks,
Paul

@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 :-)

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 ...

@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.

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.

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

Willy, thank you it solved the problem

@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.

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

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.

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

@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.

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.

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.

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

seems to work.

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

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

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.

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

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.

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.

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

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

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

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.

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)

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

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

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?

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.

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?

@ 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.

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.

thanks!! really worked like charm

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.