LinkBack URL
About LinkBacks
Since excel does not allow you to select "all caps" on the font select screen, is there a way to force the spreadsheet to make all letters / words capitalized even if a user type in lower case?
force all caps
Since excel does not allow you to select "all caps" on the font select screen, is there a way to force the spreadsheet to make all letters / words capitalized even if a user type in lower case?
Last edited by vamedic11; 12-29-2011 at 01:07 PM.
Try using Data Validation, custom option and this formula:
=EXACT(UPPER(A1),A1)
I suggest you create a custom Input message (via the DV dialog) or a custom Error Alert.
Be sure the Alert style is set to stop.
Palmetto
Do you know . . . ?
You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.
Only economically by using VBa in the Sheet Module Change Event, or the Woorkbook SheetChange Event.
e.g. In the Sheet Module
You could use WorksheetFunction.Upper(Target) to force all capitals or WorksheetFunction.Lower(Target) for all lower case.
Please Login or Register to view this content.
This of course will fail if the workbook is opened without enabling macros.
[EDIT]
I have used Ucase(Target) and Lcase(Target) in this demo. Type anything in any cell.
This will also cause problems with formula that return text values, so in practice it is better to control only the ranges you need to.
Last edited by Marcol; 12-27-2011 at 12:18 PM.
If you need any more information, please feel free to ask.
However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....
Also
اس کی مدد کرتا ہے اگر
شکریہ کہنے کے لئے سٹار کلک کریں
If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.
Events, Marcol, Events ...
Entia non sunt multiplicanda sine necessitate
You could select the entire sheet pick a font that looks like all caps. (try CarbonBlock)
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Indeed shg ... and consequenses ...
Hey all. thanks for the responses. I tried Marcol's code but am now getting a runtime error in another part of the same macro. I am trying to troubleshoot now
Going crazy here
When I put the code Marcol provided
in ThisWorkbook, it has no effect. When I add it to sheet1 I get the following error message. "Compile error: ambiguous name detected: Worksheet_Change"Please Login or Register to view this content.
I did have existing code in both, so it may be a syntax error on my part but have been unable to figure it out. Below is the full code in Sheet1, including Marcol's addition.
I am also attaching the whole workbook for reference if needed.Please Login or Register to view this content.
Good morning vamedic11
Each worksheet can only have one module for each type of event. The "ambiguous name" error is symptomatic of this. That said, the one worksheet change module you are allowed can do as many things as you want it to - they will all happen when the worksheet is changed.
Just merge the two pieces of code and all should be well.
HTH
DominicB
Please familiarise yourself with the rules before posting. You can find them here.
Hi,
Press Alt+F11 this will take you to VBE screen, in the left hand side you can see the word "This Workbook" double click on it. Now at the right hand side you can see two drop down. In the first drop down, choose "Workbook", in the second dropdown choose "SheetChange". Thsi will display something like the one listed below,
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
End Sub
Now in between these two line paste the single line code which is already given in this thread, which is
If Not IsNumeric(Target) Then Target = UCase(Target)
That's it you are good to go.
Regards,
Hariharan R
Re: force all caps
With many, many thanks, I think I finally got it.
This is the code as it now reads:
Everything appears to be working nowPlease Login or Register to view this content.
Before I close this thread out, I did want to be sure this was the correct way to merge the two pieces of code. Does it look proper?
In addition to that said by others
Your code is longer than needs be, see this example for the Sheet2 code, note how to use Select Case
Because you have Data Validation in Row 5 I think this would also workPlease Login or Register to view this content.
Also remember that the sheet module refers to that sheet only therefore no need to exclude other sheetsPlease Login or Register to view this content.
e.g. This is redundant
Excepting when used in the workbook module when it becomesPlease Login or Register to view this content.
In all probability it could all be done in the Workbook ModulePlease Login or Register to view this content.
Last edited by Marcol; 12-29-2011 at 07:25 AM.
Marcol and all, MANY thanks for all the help. Everything is working smooth again! I have added reputation points to each of the responses.
Thank you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks