Forum Discussion

Greg Daughenbaugh's avatar
Greg Daughenbaugh
Copper Contributor
Nov 25, 2017

Microsoft Excel keeping all cells uppercase

I have a spreadsheet that several people use and would like to keep the text in a particular column always set to upper case.  Regardless of case the user enters the data in this column it is saved i...
excel
Excel Online
Haytham Amairah's avatar
Haytham Amairah
Silver Contributor
Nov 25, 2017

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.

 

  • mrpotatohead's avatar
    mrpotatohead
    Copper Contributor
    Jul 10, 2021

    Haytham Amairah Hi, this does not seem to work for me, even in column A when I directly copy and paste the code and saved as suggested.

     

    Any ideas?

  • richbraithwaite's avatar
    richbraithwaite
    Copper Contributor
    May 11, 2020

    The VBA code is perfect  - thanks. However, how can I get it to make all cells in 2 colums, e.g. A and C, uppercase, rather than just 1 column?

     

    Thanks in advance for any help

    • Haytham Amairah's avatar
      Haytham Amairah
      Silver Contributor
      May 13, 2020

      richbraithwaite

       

      Hi,

       

      Please try the code on column C.

      The code is somehow applied to all columns not only column A!

       

      To limit it to column A & C, try this one instead:

      Private Sub Worksheet_Activate()
    On Error Resume Next
    Application.ScreenUpdating = False
     
    Union(Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row), _
          Range("C1:C" & Range("C" & Rows.Count).End(xlUp).Row)).Select
    
    Dim cell As Range
    For Each cell In Selection
        cell.Value = UCase(cell.Value)
    Next cell
    
    Range("A1").Select
    On Error GoTo 0
    Application.ScreenUpdating = True
End Sub
  • SM_Talal's avatar
    SM_Talal
    Copper Contributor
    Jul 04, 2019

    HI Haytham,

     

    Hope you are doing well, I need one solution if it is possible;

     

    I want to change a sentence written in a single cell to PROPER Case except  the word(s) written in CAPITAL Letter.

     

    For E:g 1,

    From, "I love APPLE" To, "I Love APPLE"

     

    For E:g 2,

    From, "united arab emirates (UAE)" To, "United Arab Emirates (UAE)"

     

     

     

     

     

     

     

     

    Haytham Amairah 

Resources