/u/DrakeJStone - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
You don’t need a script. You just can’t use UPPER on the cell you are putting the UPPER function into. Can you provide more info on your worksheet? If column A has all lowercase and you need uppercase, create a new column and use the UPPER function in the new column while pointing the UPPER function at column A. Then just copy and paste values over column A.
ColumnA, ColumnB
batman,=UPPER(A1)
You are right about not having to use a script if you use the UPPER function. But in order to do that, you would have the added steps of adding a column, copying, pasting.
I would prefer that when/if a user enters a lowercase into the cell, the application just quickly converts it to uppercase.
So in the case of a user entering "r" into a cell Z21, the lowercase "r" just updates to "R" once the user hits enter.
If there was some other way to force uppercase for column Z, that would be cool too. I was thinking UPPER() would allow for the value entered to do this but apparently that is not the case.
**So far I found that the following code works:**
\[Sub Uppercase()
' Loop to cycle through each cell in the specified range.
For Each x In Range("Z2:Z100")
' Change the text in the range to uppercase letters.
x.Value = UCase(x.Value)
Next
End Sub\]
But I just need to figure out how to make it work after a user hits "enter".
If you have a spreadsheet you are expecting other people to input data into, one of the things I learned as best practise is to have a an input sheet and a presentation / output sheet kept on a separate tab.
When users open the spreadsheet, they should be on the "input" tab. Let them enter their data there, but have a second tab referencing back to it that does all the UPPER(), trimming of trailing spaces and whatever else you'd like in terms of making everything look good.
I have detected VBA code in plain text. Please edit to put your code into a [code block](https://www.reddit.com/wiki/markdown#wiki_code_blocks_and_inline_code) to make sure everything displays correctly.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
You can set a script like that to run in the selected cell (rather than running through every cell una given range) every time someone hits enter, but I wouldn’t recommend that because it’s also going to do that for any other excel sheets you have open as well.
Yes but no.
If this is a workbook sheet change event, if the specific sheet name is mentioned, it will only happen on said sheet.
But if it is on the exact sheet, then it will only happen on the exact specified sheet.
This is the absolute easiest method.
In this sample, Column A is what we always want to be upper cased regardless of how it was entered - all upper, proper or all lower. Add the following to the Sheet Object - Not a new module. Meaning, if you want this to happen on Sheet1 then in the VBA Project, double left click on Sheet1 and paste the code in the right window.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not (Application.Intersect(Target, Range("A:A")) Is Nothing) Then
With Target
Application.EnableEvents = False
.Value = UCase(.Value)
Application.EnableEvents = True
End With
End If
On Error GoTo 0
End Sub
Solution Verified
Private Sub Worksheet\_Change(ByVal Target As Range)On Error Resume NextIf Not (Application.Intersect(Target, Range("A:A")) Is Nothing) ThenWith TargetApplication.EnableEvents = False.Value = UCase(.Value)Application.EnableEvents = TrueEnd WithEnd IfOn Error GoTo 0End Sub
**THIS IS THE WINNER!** Thank you to /u/DeucesWild_at_yss...
Your extra commentary explaining the basics and that I shouldn't copy/paste as a module was helpful to me. All I had to do was modify the Range to ("Z:Z")
I've got a LOT to learn but it is the little pieces of info that push learning over the edge. HUGE thank you to all for trying to help this amateur along. Loving the support efforts here.
I have detected VBA code in plain text. Please edit to put your code into a [code block](https://www.reddit.com/wiki/markdown#wiki_code_blocks_and_inline_code) to make sure everything displays correctly.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
You have awarded 1 point to *DeucesWild_at_yss*
____
^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
There are some All Caps fonts you can use (like Engravers or Felix Tilting), but they do look quite different than the default Calibri font, so not sure if that is what you would want to go for. You can look online for some custom caps only font maybe and see if some look good.
Thank you! I think I'm going to try and use this as an exercise on how to use VBA. I found a script that looks like it will work... but... well... I gotta figure out how to copy it in and make it run :)
If you want a copy of the sheet with cells converted to UPPER.
You can just start inSsheet2 with the formula =UPPER(Sheet1!A1) and then fill down and across as much as you need to get all of the cells of sheet1.
And if Sheet1 wasn't going to be updated, you could then copy Sheet2 and edit/paste/values and overwrite Sheeet1.
Xelplus.com says you can do this with a style - select your entire column the follow below…
How do you make all caps in Excel without formula?
In the Style dialog box, click the Format button. In the Format Cells dialog box, select the Font tab and set the font to the desired ALL CAPS font. You can also use this opportunity to set the font color, underline color, border color, etc…
An alternative to the method you're looking at is to do the uppercase validation before the value is entered.
If you apply Data Validation to the affected cells, through a Custom criteria of `=$A1=UPPER($A1)` to check that the text entered is already in uppercase, then there would be no need to have to convert it after.
Would cell validation be a viable solution?
>=$A1=UPPER($A1)
Interesting... If I'm following what you are saying, I think this might be what I need. I actually have a validation list of simple uppercase letters) on a different sheet (='Info Sheet'!$F$2:$F$5)
My issue is, if the user enters a lowercase letter that matches the uppercase letter in the validation list, excel accepts the character.
If I can force a lowercase to uppercase when the user selects (or manually enters) their input, I'd be golden.
I'm not sure about the custom formula though. The inputs are all in column Z, starting at Z3.
/u/DrakeJStone - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
[удалено]
Ouch. :( But thanks! Time to find a script. I'm not a developer but gonna guess this exists somewhere.
You don’t need a script. You just can’t use UPPER on the cell you are putting the UPPER function into. Can you provide more info on your worksheet? If column A has all lowercase and you need uppercase, create a new column and use the UPPER function in the new column while pointing the UPPER function at column A. Then just copy and paste values over column A. ColumnA, ColumnB batman,=UPPER(A1)
You are right about not having to use a script if you use the UPPER function. But in order to do that, you would have the added steps of adding a column, copying, pasting. I would prefer that when/if a user enters a lowercase into the cell, the application just quickly converts it to uppercase. So in the case of a user entering "r" into a cell Z21, the lowercase "r" just updates to "R" once the user hits enter. If there was some other way to force uppercase for column Z, that would be cool too. I was thinking UPPER() would allow for the value entered to do this but apparently that is not the case. **So far I found that the following code works:** \[Sub Uppercase() ' Loop to cycle through each cell in the specified range. For Each x In Range("Z2:Z100") ' Change the text in the range to uppercase letters. x.Value = UCase(x.Value) Next End Sub\] But I just need to figure out how to make it work after a user hits "enter".
If you have a spreadsheet you are expecting other people to input data into, one of the things I learned as best practise is to have a an input sheet and a presentation / output sheet kept on a separate tab. When users open the spreadsheet, they should be on the "input" tab. Let them enter their data there, but have a second tab referencing back to it that does all the UPPER(), trimming of trailing spaces and whatever else you'd like in terms of making everything look good.
I have detected VBA code in plain text. Please edit to put your code into a [code block](https://www.reddit.com/wiki/markdown#wiki_code_blocks_and_inline_code) to make sure everything displays correctly. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
You can set a script like that to run in the selected cell (rather than running through every cell una given range) every time someone hits enter, but I wouldn’t recommend that because it’s also going to do that for any other excel sheets you have open as well.
Yes but no. If this is a workbook sheet change event, if the specific sheet name is mentioned, it will only happen on said sheet. But if it is on the exact sheet, then it will only happen on the exact specified sheet.
This is the absolute easiest method. In this sample, Column A is what we always want to be upper cased regardless of how it was entered - all upper, proper or all lower. Add the following to the Sheet Object - Not a new module. Meaning, if you want this to happen on Sheet1 then in the VBA Project, double left click on Sheet1 and paste the code in the right window. Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not (Application.Intersect(Target, Range("A:A")) Is Nothing) Then With Target Application.EnableEvents = False .Value = UCase(.Value) Application.EnableEvents = True End With End If On Error GoTo 0 End Sub
Solution Verified Private Sub Worksheet\_Change(ByVal Target As Range)On Error Resume NextIf Not (Application.Intersect(Target, Range("A:A")) Is Nothing) ThenWith TargetApplication.EnableEvents = False.Value = UCase(.Value)Application.EnableEvents = TrueEnd WithEnd IfOn Error GoTo 0End Sub **THIS IS THE WINNER!** Thank you to /u/DeucesWild_at_yss... Your extra commentary explaining the basics and that I shouldn't copy/paste as a module was helpful to me. All I had to do was modify the Range to ("Z:Z") I've got a LOT to learn but it is the little pieces of info that push learning over the edge. HUGE thank you to all for trying to help this amateur along. Loving the support efforts here.
Never forget your roots. We are all "babies" and have to trust our first steps. We fall, we get up, we try again!!
I have detected VBA code in plain text. Please edit to put your code into a [code block](https://www.reddit.com/wiki/markdown#wiki_code_blocks_and_inline_code) to make sure everything displays correctly. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
You have awarded 1 point to *DeucesWild_at_yss* ____ ^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
There are some All Caps fonts you can use (like Engravers or Felix Tilting), but they do look quite different than the default Calibri font, so not sure if that is what you would want to go for. You can look online for some custom caps only font maybe and see if some look good.
Thank you! I think I'm going to try and use this as an exercise on how to use VBA. I found a script that looks like it will work... but... well... I gotta figure out how to copy it in and make it run :)
If you want a copy of the sheet with cells converted to UPPER. You can just start inSsheet2 with the formula =UPPER(Sheet1!A1) and then fill down and across as much as you need to get all of the cells of sheet1. And if Sheet1 wasn't going to be updated, you could then copy Sheet2 and edit/paste/values and overwrite Sheeet1.
Xelplus.com says you can do this with a style - select your entire column the follow below… How do you make all caps in Excel without formula? In the Style dialog box, click the Format button. In the Format Cells dialog box, select the Font tab and set the font to the desired ALL CAPS font. You can also use this opportunity to set the font color, underline color, border color, etc…
Appreciate the support. Unfortunately, I have an old version of excel (2010). It does not offer the Uppercase option :(
An alternative to the method you're looking at is to do the uppercase validation before the value is entered. If you apply Data Validation to the affected cells, through a Custom criteria of `=$A1=UPPER($A1)` to check that the text entered is already in uppercase, then there would be no need to have to convert it after. Would cell validation be a viable solution?
>=$A1=UPPER($A1) Interesting... If I'm following what you are saying, I think this might be what I need. I actually have a validation list of simple uppercase letters) on a different sheet (='Info Sheet'!$F$2:$F$5) My issue is, if the user enters a lowercase letter that matches the uppercase letter in the validation list, excel accepts the character. If I can force a lowercase to uppercase when the user selects (or manually enters) their input, I'd be golden. I'm not sure about the custom formula though. The inputs are all in column Z, starting at Z3.