Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Mar 2003
    Location
    Toronto, Ontario, Canada
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Format to CAPS (XP )

    It there a way of formatting cells in Excel so that text that is entered will appear in capital letters?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Format to CAPS (XP )

    Not directly with a format.
    You can use the formula:
    <pre>=upper(a1)</pre>

    to put into another cell the contents of A1 all uppercase

    The other alternative is a macro button to select a range and the macro would turn all the cells into upper case:

    <pre>Option Explicit
    Sub SelectionProper()
    Dim rCell As Range
    For Each rCell In Selection
    rCell.Value = ucase(rCell.Value)
    Next rCell
    End Sub</pre>


    If you wanted it automatic, you would have to create a macro like below to check the changed cells and uppercase them if desired. The following will change anything entered into col A into upper case automagically.

    The macro needs to go into the sheet object of the sheet where you want to do this. If you only want to do a different range change as appropriate.
    <pre>Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
    Application.EnableEvents = False
    Dim rcell As Range
    For Each rcell In Intersect(Target, Range("A:A"))
    rcell.Value = UCase(rcell.Value)
    Next rcell
    End If
    Application.EnableEvents = True
    End Sub</pre>


    Steve

  3. #3
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Format to CAPS (XP )

    This is a macro I keep in my personal.xls
    <pre>Sub ToggleCase()
    'toggles case
    Dim Rng As Range
    For Each Rng In Selection.Cells
    If Rng.HasFormula = False Then
    If Rng.Value = StrConv(Rng.Value, vbProperCase) Then
    Rng.Value = StrConv(Rng.Value, vbLowerCase)
    ElseIf Rng.Value = StrConv(Rng.Value, vbLowerCase) Then
    Rng.Value = StrConv(Rng.Value, vbUpperCase)
    ElseIf Rng.Value = StrConv(Rng.Value, vbUpperCase) Then
    Rng.Value = StrConv(Rng.Value, vbProperCase)
    End If
    End If
    Next Rng
    End Sub
    </pre>

    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  4. #4
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Appleton, Wisconsin, USA
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format to CAPS (O2K SP3 )

    OK, check this out. In the attached worksheet, I would like to be able to have any "x" that is entered in the matrix be automatically capitalized. But because of the way the sheet is set up, I don't know that I'll be able to use ranges. The cells to which this would apply would be ones like: G17, K13, O10, 017, etc. and then four ranges: A5:C7, A33:C35, BD5:BU7 and BD33:BU35.

    Also, since I'm in O2K rather than OXP, will this macro work?

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format to CAPS (O2K SP3 )

    Does the attached do what you want?
    Legare Coleman

  6. #6
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Appleton, Wisconsin, USA
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format to CAPS (O2K SP3 )

    YES!! Perfect!

    How did you do that?

    I get prompted with my security settings for macros, but when I go to Tools, Macro, Macros, I don't see any there...

    Angela

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format to CAPS (O2K SP3 )

    The code is in the Worksheet Change event routine. Right click on the sheet tab and select view code from the pop up menu. You should see the code there.
    Legare Coleman

  8. #8
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Appleton, Wisconsin, USA
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format to CAPS (O2K SP3 )

    Very cool; that works perfectly.

    Thank you!

    Now if only there was a way to do that without using VB; I have to figure out if my end users will find the convenience to be worth the macro security messages!

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format to CAPS (O2K SP3 )

    If you put an electronic signature on your code, the user will only get the security message once if they respond to trust that signature.
    Legare Coleman

Posting Permissions

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