All Text In Capitals

-emma-

Board Regular
Joined
Jul 14, 2006
Messages
174
Office Version
  1. 365
Platform
  1. Windows
Hey,
This is probably really easy but its got me stumped.
I have a spread sheet where other users fill in a column but need this column to always display in capitals. I know the =UPPER() formula but that isnt right for what i need.
Any help is welcome.
Thanks.
Emma x
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
57,364
Office Version
  1. 365
Platform
  1. Windows
Assuming the column is column A, try this:

1. Select the whole column by clicking its heading label.
2. Data|Validation...|Settings tab|Allow: Custom|Formula: =EXACT(A1,UPPER(A1))|OK

That should force upper case inputs in the first place. Is that any use?
 

-emma-

Board Regular
Joined
Jul 14, 2006
Messages
174
Office Version
  1. 365
Platform
  1. Windows
This looks like it would work but i was looking for something that would do it automaticly. E.g. If i was to type hello it would replace it with HELLO. Any other suggestions?
Thanks,
Emma x
 

ADAMC

Well-known Member
Joined
Mar 20, 2007
Messages
1,169
Office Version
  1. 2013
Platform
  1. Windows
does this help

Hi Emma,

Heres something i have to force upper case in column A range can be changed if needed

Right click your sheet name tab and click view code paste the following:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub

    On Error Resume Next
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        Application.EnableEvents = False
        Target = UCase(Target)
        Application.EnableEvents = True
    End If
    On Error GoTo 0
End Sub

hope it helps
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
57,364
Office Version
  1. 365
Platform
  1. Windows
This looks like it would work but i was looking for something that would do it automaticly. E.g. If i was to type hello it would replace it with HELLO. Any other suggestions?
Thanks,
Emma x
You would have to use vba for this.

1. Right click the sheet name tab and choose "View Code"
2. Copy the code below and paste in the main VB window that opens in step 1.
3. Close the VB window.
4. Try entering in the sheet.

If you will be entering any formulas in the sheet, it would be best to modify this code to restrict its actions just to the area where data that needs capitalising will be entered. Post back with more details if you need help with this.

Edit: Woops, forgot the code..

<font face=Courier New><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">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    Target.Value = UCase(Target.Value)
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 

-emma-

Board Regular
Joined
Jul 14, 2006
Messages
174
Office Version
  1. 365
Platform
  1. Windows
Thanks both for the replies. I used ADAMC's reply cos it was the first i saw and it works fine. Thanks again to you both.

Emma x
 

Boller

Banned
Joined
Apr 11, 2006
Messages
2,328
Another way, without using VBA, is to format column A in a "capitals" font (e.g. Felix Titling)


If using VBA, to cover the possibility that the target consists of more than one cell :-

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, cell As Range
Set rng = Intersect(Target, [A:A])
If Not rng Is Nothing Then
    Application.EnableEvents = False
    For Each cell In rng
        cell = UCase(cell)
    Next
    Application.EnableEvents = True
End If
End Sub


And if column A might contain formulas :-

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, cell As Range, fmla As String
Set rng = Intersect(Target, [A:A])
If Not rng Is Nothing Then
    Application.EnableEvents = False
    For Each cell In rng
        If cell.HasFormula Then
            fmla = Right(cell.Formula, Len(cell.Formula) - 1)
            If Left(cell.Formula, 6) <> "=UPPER" Then
                If cell.HasArray Then
                    cell.FormulaArray = "=UPPER(" & fmla & ")"
                Else
                    cell.Formula = "=UPPER(" & fmla & ")"
                End If
            End If
        Else
            cell = UCase(cell)
        End If
    Next
    Application.EnableEvents = True
End If
End Sub
 

Forum statistics

Threads
1,181,607
Messages
5,930,881
Members
436,764
Latest member
avalladarez

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