All Text In Capitals

[-emma-]

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

 

[Peter_SSs]

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-]

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]

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:

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]

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-]

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]

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 :-

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 :-

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