Forum Discussion
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...
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.
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
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
