VBA TO CONVERT TEXT TO UPPERCASE

egris52788

Board Regular
Joined
Mar 6, 2003
Messages
114
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Try this:

Code:
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
 
Upvote 0
Here is another version that won't have any screen flickering:

Code:
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
 
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0
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
 
Upvote 0
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

Code:
[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]
 
Last edited:
Upvote 0
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?
 
Upvote 0
Welcome to the MrExcel board!

Could you post your entire code?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top