Microsoft Excel keeping all cells uppercase

greg, I Love this code. I also use it to change a cells contents to MAC Address format. Is there a way to get the code to activate when the active cell looses focus, rather than having to change worksheets back and forth? (i.e. I press enter after entering data and the focus shifts to the next cell down)

Greg Daughenbaugh 

This is a very special situation by the way...

The solutions provided by @Haytham Amairah are great.

However you want to treat the different words in the same cell differently!!

I suggest:

1- Select the range (assuming column A) that you want to fix >> Data Tab >> Text To Column

2-  Data Tab >> Text To Columns (Should have blank columns to the Right) >> Check "Delimiter" >> Check "Space" >> Finish

3- In Cell D1 write the Formula that fixes the capitalization as desired

'=IF(NOT(EXACT(LOWER(A1),A1))=FALSE,PROPER(A1),A1)&" "& IF(NOT(EXACT(LOWER(B1),B1))=FALSE,PROPER(B1),B1)&" "& IF(NOT(EXACT(LOWER(C1),C1))=FALSE,PROPER(C1),C1)

4- Copy the formula down

5- Copy All results in Column D >> Then Paste Values (CTRL + C >> ALT E S V >> Enter)

6- Delete the Columns A, B, C

Hope that helps

Nabil Mourad

You can use another way, it's forcing users from the beginning to enter uppercase characters through Data Validation feature.

Just follow these steps:

• Select the entire column you want.
• Go to Data Ribbon Tab >> Data Tools Group >> Data Validation

• From Data Validation dialog box, select custom from Allow combo box.
• In the formula bar copy and paste this formula:

=EXACT(A1,UPPER(A1))

• Press OK.

After that, users are not allowed to enter lowercase characters in column A.

NOTE: If you want to apply this to a column other than column A, select that column entirely, and change the cell references in data validation formula.

Greg,

This is possible, but with some lines of code!

I have a macro which is fairly good to do this task, to apply it follow these steps:

Step 1: Press Alt+F11 to open the VBA editor as shown in the below screenshot:

Step 2: Double-click on the sheet that contains the column you want.

Step 3: Copy this code to the editor, then close it.

Private Sub Worksheet_Activate()
On Error Resume Next
Application.ScreenUpdating = False

Dim cell As Range

For Each cell In Range("$A$1:" & Range("$A$1").SpecialCells(xlLastCell).Address)
    cell.Value = UCase(cell.Value)
Next cell

On Error GoTo 0
Application.ScreenUpdating = True
End Sub

Step 4: Save the workbook as Macro-Enabled Workbook (*.xlsm).

After that, the macro will trigger each time you activate the worksheet.

NOTE: This macro is applied to column A in sheet1.