Capitalizing

[MalR]

Hi guys. I have a template 40 rows long and 12 cols wide and I would like a number of the cells in it to be capitalized so that when I fill it out the text in these cells show as capitals. I understand that this is difficult as Excel has not allowed for it but has anyone worked out a solution? The cells can be in several places in the table so not able to use =UPPER(A2).
I am using Office 365 on Win 10.
Thanks in advance

 

[chirayu]

The only way I can think of is through a macro. To use macro below. Select the cells you want to be uppercase and run

Sub UpperCase()

Dim rng As Range
Dim subrng As Range

Set rng = Selection
For Each subrng In rng
    subrng = UCase(subrng)
Next

End Sub

 

[vletm]

MalR
... or could You use <CAPS LOCK>

 

[GraH - Guido]

Or since Office 365 comes with PowerQuery on board, you don't care how it is entered. You just clean up and standardize afterwards with a single click.

 

[VDS]

Dear friend,

A small suggestion.

Select the area you want.

Press ALT + F11
Go to command window.

TYPE :

selection.value=evaluate("if(row(),upper(" & selection.address & "))") & ENTER.

The desired data will be capitalized.

PLEASE REMEMBER ONE THING CAREFULLY. IT CAN NOT BE CONVERTED INTO BACK AS IT IS A MACRO COMMAND.

TRY THIS AND SEE THE RESULT.

ALSO SEE MY OLD POST IN THE NAME OF

Convert into UPPER Case with a single command / macro


VDS

 

[MalR]

MalR
... or could You use <CAPS LOCK>

Thanks vletm. Can you explain how <CAPS LOCK> is done please. It looks interesting.

 

[vletm]

MalR
Press this as needed...

 

[Peter Bartholomew]

The only way I can think of is through a macro. To use macro below. Select the cells you want to be uppercase and run

@chirayu's macro could be adjusted to operate over a predefined range rather than a user selection. If the worksheet range were named 'upper' then it would require

Set Rng = [upper]

or, if you want to trigger the macro with a worksheet change event, you could use the intersection of the ranges 'Target' and Range("upper").

 

[MalR]

MalR
Press this as needed...
View attachment 56251

Thanks vletm. I thought it was something different. I need something that I can highlight a number of cells in a table in a template and make them capitalize. So when I type some text in these cells they will show as caps. I drag the template along to duplicate therefore the formula or code must repeat

 

[vletm]

MalR
Why You would like to 'clean' those afterwards?
Why You d/won't take care 'capitalizing' as soon as possible? = before enter.
You could use fonts, which are all 'UPPER' eg 'Engraves MT'.
There could be more of those fonts too.
Then still no need to use macros.

 

[Peter Bartholomew]

If you want the uppercase to propagate to new cells with copy then you could define a style 'upper' that either flags the cell for VBA processing

   If subRng.Style = "Upper" Then subRng = UCase(subRng)

or even, as @vletm suggests, is defined to use an uppercase only font. I found 'Stencil' but perhaps that give an excessive level of emphasis for the content.

 

[MalR]

MalR
Why You would like to 'clean' those afterwards?
Why You d/won't take care 'capitalizing' as soon as possible? = before enter.
You could use fonts, which are all 'UPPER' eg 'Engraves MT'.
There could be more of those fonts too.
Then still no need to use macros.

MalR
Why You would like to 'clean' those afterwards?
Why You d/won't take care 'capitalizing' as soon as possible? = before enter.
You could use fonts, which are all 'UPPER' eg 'Engraves MT'.
There could be more of those fonts too.
Then still no need to use macros.

Great suggestion vletm and Peter! I am always looking at formulas and macros to get stuff done. Never crossed my mind to change the font. Sometimes the simplest solution is the easiest.
Thank you to all who replied. This can be closed now.