Page 1 of 2 12 LastLast
Results 1 to 10 of 14
Like Tree1Likes

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
    73,092

    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
    35,499

    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
    507

    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
    15,112

    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 09:12 AM.
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  8. #8
    New Member
    Join Date
    Jul 2016
    Posts
    9

    Default Re: VBA TO CONVERT TEXT TO UPPERCASE

    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?

  9. #9
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    33,507

    Default Re: VBA TO CONVERT TEXT TO UPPERCASE

    Welcome to the MrExcel board!

    Could you post your entire code?
    Hope this helps, good luck.
    Peter
    Excel 2010 (mainly), 2016, 2013, 2007, 2003 - Windows 10, 7
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules & Forum Use Guidelines

  10. #10
    New Member
    Join Date
    Jul 2016
    Posts
    9

    Default Re: VBA TO CONVERT TEXT TO UPPERCASE

    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.

Page 1 of 2 12 LastLast

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