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

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

 

[b9k9uk]

New kid on the block here, can someone help please?

That's odd because I have:

Dim DriverName as String

UCase(DriverName)

and get the error

Compile error:

Can't find project or library

So how do I convert the variable DriverName to Upper case?

 

[Peter_SSs]

Welcome to the MrExcel board!

Could you post your entire code?

 

[b9k9uk]

Thanks for the prompt reply!

Sub AddorRemoveDrivertoList()
'
' Add or remove driver names from rota list
'
Dim Selection As String
Dim DriverName As String
Dim rn As Integer
Dim Again As Integer


Selection = InputBox("Do you want to add or remove a driver A or R?")

If Selection = "A" Or Selection = "a" Then
Sheets("DriverNames").Select
End If

Do
DriverName = InputBox("What is the name of the Driver you are adding?")

DriverName = Application.WorksheetFunction.Proper(DriverName)

AddDriverStart:

rn = 1

Do
rn = rn + 1
Range("A" & rn).Select
Range("D" & rn).Value = rn
Range("E2").Value = DriverName

If Range("A" & rn).Value = DriverName Then

DriverName = InputBox _
("Driver already on list, please enter a new name?")

GoTo AddDriverStart

ElseIf Range("A" & rn).Value = "" Then
Range("A" & rn).Value = DriverName
End If

Loop Until Range("A" & rn).Value = DriverName

Range("A" & rn).Font.Name = "Calibri"
Range("A" & rn).Font.Size = 8
Range("A" & rn).Value = DriverName

Again = MsgBox("Do you want to add another Driver?", vbYesNo)

If Again = vbNo Then
Sheets("Main Page").Select
End If

Loop Until Again = vbNo

End Sub
Sub DriverName_Change()
'
' Convert DriverName to Upper Case
'
Dim DriverName As String


DriverName = UCase(DriverName)


Return


End Sub

I have been using DriverName = Application.WorksheetFunction.Proper(DriverName) but this only sets the initial as first letter of the surname as caps. I would much prefer all caps.