VBA TO CONVERT TEXT TO UPPERCASE

[egris52788]

i need a macro that will change an entire sheet to uppercase. we are not allowed to download utilities, therefore I cant use asap utilities, i need to put the code directly into my workbook. thanks

[Andrew Poulsom]

Try this:

Sub Test()
    Dim Rng As Range
    Dim c As Range
    On Error Resume Next
    Set Rng = Cells.SpecialCells(xlCellTypeConstants, 2)
    For Each c In Rng
        c.Value = UCase(c.Value)
    Next c
End Sub

[Joe4]

Here is another version that won't have any screen flickering:

Sub MyUpperCase()
    
    Application.ScreenUpdating = False

    Dim cell As Range
    For Each cell In Range("$A$1:" & Range("$A$1").SpecialCells(xlLastCell).Address)
        If Len(cell) > 0 Then cell = UCase(cell)
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub

[Mat]

and add this one if you need the cell containing formula to be in uppercase too...

Dim str As String
Dim rg As Range

For Each rg In Cells.SpecialCells(xlCellTypeFormulas, 23)
str = rg.Formula
str = "=UPPER(" & Right(str, Len(str) - 1) & ")"

rg.Formula = str

Next

[Orionpowerbook]

Good day all
Im new at this but is there a way to make is so that it automatically does it with out having to hit the alt+ f8 ???

like if I enter "hello world" in D4 that after hitting enter or moving to an other cell it would auto convert to "HELLI WORLD" ???


thanks...

[etuskan]

Good day all
Im new at this but is there a way to make is so that it automatically does it with out having to hit the alt+ f8 ???

like if I enter "hello world" in D4 that after hitting enter or moving to an other cell it would auto convert to "HELLI WORLD" ???


thanks...

1. Alt + F11
2. Select the Sheet from the VBA Explorer panel in the left In Case it doesnt appear click CTRL + R
3. Double click the Sheet where you want your code work
4. Select "Worksheet" instead "General" from the combobox above
5. Select "Change" as an event from the combobox above

Below is an example of change event which is triggerede whenever sthg. haschanged in the appropriate sheet..

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "Change Event"
End Sub

[AlphaFrog]

Good day all
Im new at this but is there a way to make is so that it automatically does it with out having to hit the alt+ f8 ???

like if I enter "hello world" in D4 that after hitting enter or moving to an other cell it would auto convert to "HELLI WORLD" ???


thanks...

• Right-click on your sheet tab
• Select View Code from the pop-up context menu
• Paste the code below in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell   As Range
    On Error Resume Next
    Application.EnableEvents = False
    For Each cell In Target
        cell = UCase(cell)
    Next
    Application.EnableEvents = True
End Sub