Can you force a cell to display CAPS

j4ymf

Well-known Member
Joined
Apr 28, 2003
Messages
647
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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,249
Office Version
  1. 365
Platform
  1. Windows
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.
 

j4ymf

Well-known Member
Joined
Apr 28, 2003
Messages
647
Office Version
  1. 365
Platform
  1. Windows
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???
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473

ADVERTISEMENT

Whack ATL+F11 to bring up the VBA Editor, then select from the left hand pane and paste to the center/middle pane.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,249
Office Version
  1. 365
Platform
  1. Windows
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.
 

j4ymf

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

ADVERTISEMENT

thanks guys i would be lost with out this forum. keep up the good work
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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! ;)
 

j4ymf

Well-known Member
Joined
Apr 28, 2003
Messages
647
Office Version
  1. 365
Platform
  1. Windows
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
 

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345
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?
 

Forum statistics

Threads
1,136,268
Messages
5,674,734
Members
419,523
Latest member
Urnovio

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
Top