Results 1 to 6 of 6

Uppercase format?

This is a discussion on Uppercase format? within the Excel Questions forums, part of the Question Forums category; Is there any way to format a cell so that all entry into the cell will be in uppercase? Is ...

  1. #1
    Board Regular
    Join Date
    Jul 2002
    Posts
    60

    Default

    Is there any way to format a cell so that all entry into the cell will be in uppercase?

    Is there a custom format that can be used?
    Or does it have to be VBA?

    Thanks,

    P

  2. #2
    Board Regular Haluk's Avatar
    Join Date
    Oct 2002
    Location
    Turkiye
    Posts
    832

    Default

    Hi;

    For cell A1, you can force the user to enter only UPPER case letters by;

    Data | Validation >> Allow : Custom, Formula : =exact(a1,upper(a1))

    Then, select cell A1 by your mouse and copy down.

  3. #3
    Board Regular
    Join Date
    Jul 2002
    Posts
    60

    Default

    I'm already using validation on this column to insure that only Y or N is entered. Can I have multiple validations on the same column?

  4. #4
    Board Regular
    Join Date
    Aug 2002
    Location
    Newcastle
    Posts
    382

    Default

    Put the below code in to your project. Making sure that you put the Options in "Declarations". Set the range to the cells that you require to be set in capitals. the code should then automatiacally change any lowercase text in to uppercase.

    This is the only way of doing this i know but i would be interested in any other methods


    Option Explicit 'to prevent erros arising from typos
    Option Compare Binary 'make string comparisions case sensitive (is default, but...)

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    'Ensures that any entry/change in indicated range is written in uppercase
    'Place this code at _worksheet_ level of monitored worksheet

    If Intersect(Target, Range("A1:A30")) Is Nothing Then '<< CHECK
    'Do absolutely nothing
    Else
    With Target
    If UCase(.Text) <> .Text Then
    .Value = UCase(.Text)
    End If
    End With
    End If
    End Sub


  5. #5
    New Member
    Join Date
    Nov 2010
    Posts
    47

    Default Re: Uppercase format?

    Sorry to bump old thread but this came up for me in Google so others might see it.

    Poster above his the right idea... more concisely, stick this in your Worksheet code:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        
    If Not Intersect(Target, Range("I3:I7")) Is Nothing Then Target = UCase(Target)
    
    End Sub
    Also worth mentioning is that if you're performing some intense calculations you can improve performance by around 30% turning off the checking while you're calculating / updating the sheet by including

    Code:
    Application.EnableEvents = False
    at the start of your routine. (And obviously set it to True at the end.)
    Last edited by snowjoke; Nov 24th, 2010 at 06:39 PM.

  6. #6
    New Member
    Join Date
    Jan 2012
    Posts
    1

    Default Re: Uppercase format - Working with any selected range

    2 years later ...
    Searching for a similar solution, found this post, and implemented one more Universal solution, that I would like to share.

    In order to make the UPcase conversion, work for any preselected range,
    we can create this Procedure:

    Public Sub Format_Upcase()
    Application.ScreenUpdating = False 'speed up with large selections
    Application.Calculation = xlManual

    For Each xCell In Selection
    If UCase(xCell.Text) <> xCell.Text Then xCell.Value = UCase(xCell.Text)
    Next

    Application.Calculation = xlAutomatic
    Application.ScreenUpdating = True
    End Sub


    Then, we just have to select the range where we want to change to Upcase and run the macro...

    Hope it helps,

    Regards

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