# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  > [SOLVED] Automatically change text to uppercase

## Santie

I am creating a data sheet to be completed by other users.  I would like to
format the text cells (name, etc) to have text entered as uppercase
automatically although the user might use title or lower case.

UPPER function cannot make cell look at itself and perform the function

Excel 2003

----------


## masim

Hi

this can be done through data validation - forcing a user to use upper case

Suppose you want to have upper case entry is cell C8

goto data>validation

Tab: settings
Allow: custom
formula: =EXACT(C8,UPPER(C8))

Copy formats accross all cells that you want to be shown in upper case.

Hope that helps.

Regards
Asim

----------


## Maglor

You could use this VBA-code:

Sub Uppercase()
' Loop to cycle through each cell in the specified range.
For Each x In Range("A1:B10")
' Change the text in the range to uppercase letters.
x.Value = UCase(x.Value)
Next
End Sub

It'll have to be run after the data has been entered.

On Tue, 22 Feb 2005 01:25:02 -0800, Santie
<Santie@discussions.microsoft.com> wrote:

>I am creating a data sheet to be completed by other users.  I would like to
>format the text cells (name, etc) to have text entered as uppercase
>automatically although the user might use title or lower case.
>
>UPPER function cannot make cell look at itself and perform the function
>
>Excel 2003

----------


## Gord Dibben

Santie

"Automatic" would require VBA code.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column > 8 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
ErrHandler:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code".

Copy/paste the above code into that module.

Note: as written it operates only on Columns A through H.

Change the > 8 to something else if you require more or less columns.


Gord Dibben  Excel MVP

On Tue, 22 Feb 2005 01:25:02 -0800, Santie <Santie@discussions.microsoft.com>
wrote:

>I am creating a data sheet to be completed by other users.  I would like to
>format the text cells (name, etc) to have text entered as uppercase
>automatically although the user might use title or lower case.
>
>UPPER function cannot make cell look at itself and perform the function
>
>Excel 2003

----------


## rtorresgzz

Hello, I'm new in the community.. Would appreciate some help.. I have a file I send my customers for them to fill... I want to automatically have their input text changed to uppercase... I've tried with several vba codes on the internet but none will work, is their a setting or configuration I might be missing?

----------


## protonLeah

rtorresgzz,
Unfortunately _your post does not comply with Rule 2 of our Forum_ RULES. *Do not post a question in the thread of another member -- start your own thread.* 

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread. 

Old threads are often only monitored by the original participants.  New threads not only open you up to all possible participants again, they typically get faster response, too.

----------


## wheeljawk

I modified the code found here,...
docs.microsoft.com/en-us/office/troubleshoot/excel/run-macro-cells-change
...so that the program would not endlessly loop.

In my case, I was looking to change the contents of cell A2 to uppercase, if not already uppercase, whenever the contents of that cell were changed. 
here is the modified code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("A2")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then

    ' Change cell contents to UPPER case
        If Range("A2").Value <> UCase(Range("a2").Value) Then
            Range("A2").Value = UCase(Range("A2").Value)
        Else
            End
        End If
    End If
End Sub

----------

