Can you force a cell to display CAPS

j4ymf

Well-known Member
Joined
Apr 28, 2003
Messages
740
Office Version
  1. 365
Platform
  1. Windows
hello again

i was wondering can you format a cell to only display CAPS only

i need say cell A1 to display ABC123 not abc123 so my sheet looks the same on who ever fills it in??


thanks jason
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sure:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range
         <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN>
        <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
        <SPAN style="color:#007F00">'   Set Target Range</SPAN>
        Set rng = Range("A1")
        <SPAN style="color:#007F00">'   Only look at that range</SPAN>
        <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <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>
            <SPAN style="color:#007F00">'   Action if Condition(s) are met</SPAN>
            Target = UCase(Target)
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

The code goes in the sheet specific module and you can adjust the range as needed.

Hope that helps,

Smitty
 
Upvote 0
That can be done with some Event Procedure VBA.

Right click on the sheet tab name, select View Code and paste the following code in:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Target = UCase(Target)
    
End Sub
What this will do, is whenever anyone types something in any cell, it will automatically convert it to all caps.


EDIT: Smitty's code is a little better, as it accounts for multiple cell selections.
 
Upvote 0
im lost sorry!

Right click on the sheet tab name, select View Code and paste the following code in:

how and where do i do this???
 
Upvote 0
Whack ATL+F11 to bring up the VBA Editor, then select from the left hand pane and paste to the center/middle pane.
 
Upvote 0
At the bottom of your worksheet, you should see a "tab" with the name of the worksheet, i.e. "Sheet1" unless you have changed it. Simply place your mouse/cursor right over the words "Sheet1" and right click your mouse, then follow the rest of the steps.
 
Upvote 0
thanks guys i would be lost with out this forum. keep up the good work
 
Upvote 0
Right-click the worksheet tab that you want the CAPS to be forced on and select the View Code option. That opens the VBE (Visual basic Editor) When the new VBE window opens, paste the code in the window on the right hand side, then ALT+Q to exit and test.

Welcome to the world of VBA programming!

Smitty

EDIT: Too slow! Never walk down to Accouinting with these guys around! ;)
 
Upvote 0
hi i have tried this on XP and it works fine but it dosent work on 97 WHY?


can you help?


Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
         '   Only look at single cell changes
        If Target.Count > 1 Then Exit Sub
        '   Set Target Range
        Set rng = Range("A1")
        '   Only look at that range
        If Intersect(Target, rng) Is Nothing Then Exit Sub
            '   Action if Condition(s) are met
            Target = UCase(Target)
End Sub


thanks jason
 
Upvote 0
I've tried it on my installation of XL 97 and it still works fine....

Are you sure you have changed the range from simply A1, so as to cover the entire range you're interested in on the sheet?
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top