Uppercase format?

mrpaulwass

Board Regular
Joined
Jul 9, 2002
Messages
60
Is there any way to format a cell so that all entry into the cell will be in uppercase?

Is there a custom format that can be used?
Or does it have to be VBA?

Thanks,

P
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Hi;

For cell A1, you can force the user to enter only UPPER case letters by;

Data | Validation >> Allow : Custom, Formula : =exact(a1,upper(a1))

Then, select cell A1 by your mouse and copy down.
 
Upvote 0
I'm already using validation on this column to insure that only Y or N is entered. Can I have multiple validations on the same column?
 
Upvote 0
Put the below code in to your project. Making sure that you put the Options in "Declarations". Set the range to the cells that you require to be set in capitals. the code should then automatiacally change any lowercase text in to uppercase.

This is the only way of doing this i know but i would be interested in any other methods


Option Explicit 'to prevent erros arising from typos
Option Compare Binary 'make string comparisions case sensitive (is default, but...)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Ensures that any entry/change in indicated range is written in uppercase
'Place this code at _worksheet_ level of monitored worksheet

If Intersect(Target, Range("A1:A30")) Is Nothing Then '<< CHECK
'Do absolutely nothing
Else
With Target
If UCase(.Text) <> .Text Then
.Value = UCase(.Text)
End If
End With
End If
End Sub
 
Upvote 0
Sorry to bump old thread but this came up for me in Google so others might see it.

Poster above his the right idea... more concisely, stick this in your Worksheet code:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    
If Not Intersect(Target, Range("I3:I7")) Is Nothing Then Target = UCase(Target)

End Sub
Also worth mentioning is that if you're performing some intense calculations you can improve performance by around 30% turning off the checking while you're calculating / updating the sheet by including

Code:
Application.EnableEvents = False
at the start of your routine. (And obviously set it to True at the end.)
 
Last edited:
Upvote 0
Re: Uppercase format - Working with any selected range

2 years later ...
Searching for a similar solution, found this post, and implemented one more Universal solution, that I would like to share.

In order to make the UPcase conversion, work for any preselected range,
we can create this Procedure:

Public Sub Format_Upcase()
Application.ScreenUpdating = False 'speed up with large selections
Application.Calculation = xlManual

For Each xCell In Selection
If UCase(xCell.Text) <> xCell.Text Then xCell.Value = UCase(xCell.Text)
Next

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub


Then, we just have to select the range where we want to change to Upcase and run the macro...

Hope it helps,

Regards
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

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