Thanks Thanks:  0
Likes Likes:  0
Page 1 of 5 123 ... LastLast
Results 1 to 10 of 45

Thread: text in caps

  1. #1
    Guest

    Default

    I want what to make sure the text in column E is caps how can this be done? Thanks

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Clovis, NM
    Posts
    341
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Check help for UPPER

  3. #3
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    With a function:

    =upper(e1)

    Cheers, Nate

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If you want to use data validation, choose custom and
    =EXACT(A1,UPPER(A1)) for cell A1.

    Note the major validation limitations, such as these will not keep a user from pasting in lower case letters.

  5. #5
    Guest

    Default

    how about in VBA?

  6. #6
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    To test:

    myrng = Range("e9")
    If myrng = UCase(myrng) Then
    'Task if Upper case
    Else: 'task if lower case
    'i.e., range("e9") = Ucase(myrng)
    End If

    Cheers, Nate

  7. #7
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Or you can force the issue without testing:

    e.g.,

    Sub Uppers()
    Range("e1:e65536").Select
    For Each cell In Selection
    myrng = ActiveCell
    ActiveCell = UCase(myrng)
    ActiveCell.Offset(1, 0).Select
    Next
    End Sub

    Cheers, Nate

  8. #8

    Join Date
    Feb 2002
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-25 13:02, NateO wrote:
    Or you can force the issue without testing:

    e.g.,

    Sub Uppers()
    Range("e1:e65536").Select
    For Each cell In Selection
    myrng = ActiveCell
    ActiveCell = UCase(myrng)
    ActiveCell.Offset(1, 0).Select
    Next
    End Sub

    Cheers, Nate
    One word of warning.
    The above will convert any cells that contain formulas to values only.
    To convert non-formula cells only to Upper :-

    Sub Uppers()
    Dim rng As Range, cell As Range
    Set rng = Intersect(ActiveSheet.UsedRange, Columns(5))
    For Each cell In rng
    If cell.HasFormula = False Then
    cell.Value = UCase(cell.Value)
    End If
    Next cell
    End Sub

    The following will convert cells to Upper and retain any formulas if the text being returned by the formula is contained as part of the formula(eg, ="a"), but will not convert to Upper if the result is obtained indirectly (eg, by a LOOKUP formula) :-

    Sub Uppers()
    Dim rng As Range, cell As Range
    Set rng = Intersect(ActiveSheet.UsedRange, Columns(5))
    For Each cell In rng
    If cell.Formula <> "" Then
    cell.Formula = Format(cell.Formula, ">")
    End If
    Next cell
    End Sub

  9. #9

    Join Date
    Feb 2002
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Or if you want to automate it upon entries being made in column E :-

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range, cell As Range
    If Not Intersect(Selection, Columns(5)) Is Nothing Then
    For Each cell In Intersect(Selection, Columns(5))
    If cell.Formula <> "" Then
    cell.Formula = Format(cell.Formula, ">")
    End If
    Next cell
    End If
    End Sub

  10. #10

    Join Date
    Feb 2002
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Correction. Better make that :-

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng1 As Range, rng2 As Range, cell As Range
    Application.EnableEvents = False
    If Not Intersect(Selection, Columns(5)) Is Nothing Then
    Set rng1 = Intersect(Selection, Columns(5))
    Set rng2 = Intersect(ActiveSheet.UsedRange, rng1)
    For Each cell In rng2
    If cell.Formula <> "" Then
    cell.Formula = Format(cell.Formula, ">")
    End If
    Next cell
    End If
    Application.EnableEvents = True
    End Sub

Some videos you may like

User Tag List

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
  •