Change One Cell to Uppercase Automatically After User Hits Enter Key

[chelseasikoebs]

I have tried so many different codes, but I can't get them work! There will be multiple sheets in the workbook and new ones added along the way. On every sheet, it's cell E1 that is affected. I have the code below, but it doesn't work when I type "january" into cell E1. I'm wanting it to automatically change it to "JANUARY" as soon as I hit Enter. I'm using Excel 2010. Any help would be greatly appreciated!

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("e1:e1")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Target = UCase(Target)
    Application.EnableEvents = True
End Sub

 

[JoeMo]

I have tried so many different codes, but I can't get them work! There will be multiple sheets in the workbook and new ones added along the way. On every sheet, it's cell E1 that is affected. I have the code below, but it doesn't work when I type "january" into cell E1. I'm wanting it to automatically change it to "JANUARY" as soon as I hit Enter. I'm using Excel 2010. Any help would be greatly appreciated!

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("e1:e1")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Target = UCase(Target)
    Application.EnableEvents = True
End Sub

Your code looks fine. Are you sure you installed it as a worksheet module rather than a standard module, and have you enabled macros?

 

[chelseasikoebs]

It's listed under Modules and macros are enabled. I just saved it as an .xlsm and that didn't work either.

 

[chelseasikoebs]

But it doesn't show up in the macros list when I click on Macros on the ribbon.

 

[chelseasikoebs]

okay, I just double-clicked on the worksheet name and pasted the code and it worked!, but I don't want to have to do that everytime a new worksheet is created. If I copy/paste the worksheets, will the code be copied as well?

 

[chelseasikoebs]

Sorry for all the posts. It looks like it does copy the code!! Yay! THANK YOU!!!!!

 

[Smitty]

If you want it to work on all worksheets, then you can use this:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_SheetChange(<SPAN style="color:#00007F">ByVal</SPAN> Sh <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">If</SPAN> Intersect(Target, Range("E1")) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>        Target = UCase(Target)<br>    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Note that the code goes in the ThisWorkbook module, not a sheet or general module.

HTH,

 

[chelseasikoebs]

Ah, okay, great! Thank you so much for your help! I was going to deem this post as Solved, but I can't find where to do that and I didn't find the answers in the FAQs.

 

[Smitty]

RE: SOLVED

You don't. We leave posts open, so that someone else with a similar issue might be able to follow along since a lot of people will ignore posts marked "SOLVED".

All you need to do is say thanks if you got an answer that works for you.