Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Checking Capitalization (Excel 2000)

    I need to format a month showing as Sep-03 into SEP-03. So I write this:
    Sub Formatdate()
    Dim REVERSALDT As String
    REVERSALDT = Range("E6").Formula
    REVERSALDT = StrConv(REVERSALDT, vbUpperCase)

    End Sub

    In the locals window, the resulting variable REVERSALDT appears as a 37679 unformatted number. Then nothing happens. I tired a dummy macro to use the custom formatting MMM-YY approach to reveal what to do, but that didn't give me the ALL CAPS format results I need. So what steps do I need to get this to give me SEP-03 instead of Sep-03 when the user types in 9/30/03?

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checking Capitalization (Excel 2000)

    This is one approach

    Sub DateChange()
    Debug.Print UCase(Format(Now(), "DD-MMM-YYYY"))
    End Sub

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

    Re: Checking Capitalization (Excel 2000)

    Try this:

    <pre>Public Sub Formatdate()
    Dim REVERSALDT As String
    REVERSALDT = UCase(Format(Range("E6").Value, "mmm-yy"))
    End Sub
    </pre>

    Legare Coleman

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Checking Capitalization (Excel 2000)

    If you Date format the cell, Excel has no built in upper case three-letter month date cell format. Excel's cell formatting overrides the VBA formatting. I would approach this by date formatting the cell to "mm/dd/yyyy", and using the worksheet change event to apply the following formula whenever a date is entered.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim varVal As Variant
    Application.EnableEvents = False
    If Not Intersect(Target, Range("E6")) Is Nothing Then
    varVal = Target.Value
    If Len(varVal) > 0 Then
    On Error GoTo Cleanup
    Target.Formula = "=UPPER(TEXT(DATEVALUE(""" & varVal & """),""mmm, yy""))"
    End If
    End If
    Cleanup:
    Application.EnableEvents = True
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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