Results 1 to 7 of 7

VBA TO CONVERT TEXT TO UPPERCASE

This is a discussion on VBA TO CONVERT TEXT TO UPPERCASE within the Excel Questions forums, part of the Question Forums category; i need a macro that will change an entire sheet to uppercase. we are not allowed to download utilities, therefore ...

  1. #1
    Board Regular
    Join Date
    Mar 2003
    Location
    OKC
    Posts
    113

    Default VBA TO CONVERT TEXT TO UPPERCASE

    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

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    68,852

    Default Re: VBA TO CONVERT TEXT TO UPPERCASE

    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

  3. #3
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,109

    Default Re: VBA TO CONVERT TEXT TO UPPERCASE

    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
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  4. #4
    Mat
    Mat is offline
    Board Regular Mat's Avatar
    Join Date
    Sep 2003
    Location
    Montréal, Québec
    Posts
    506

    Default Re: VBA TO CONVERT TEXT TO UPPERCASE

    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
    Mat
    "I will not compromise my ideals, music or nap schedule" - Trent Lane

  5. #5
    New Member
    Join Date
    Sep 2008
    Posts
    1

    Default Re: VBA TO CONVERT TEXT TO UPPERCASE

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

  6. #6
    New Member
    Join Date
    Aug 2012
    Posts
    2

    Default Re: VBA TO CONVERT TEXT TO UPPERCASE

    Quote Originally Posted by Orionpowerbook View Post
    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

  7. #7
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    11,707

    Default Re: VBA TO CONVERT TEXT TO UPPERCASE

    Quote Originally Posted by Orionpowerbook View Post
    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:
    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
    Last edited by AlphaFrog; Sep 4th, 2012 at 08:12 AM.
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Please surround your pasted VBA code with CODE tags e.g.;
    [CODE] your VBA code here [/CODE]
    The pound # icon in the forum editor will apply the CODE tags around your selected text.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com