Results 1 to 3 of 3

Make Text Proper/Sentence Case In VBA Module

This is a discussion on Make Text Proper/Sentence Case In VBA Module within the Excel Questions forums, part of the Question Forums category; Hi Gang, Can anyone suggest some handy code that I can put in a VBA module that will convert all ...

  1. #1
    New Member
    Join Date
    Jul 2003
    Posts
    1

    Default Make Text Proper/Sentence Case In VBA Module

    Hi Gang,

    Can anyone suggest some handy code that I can put in a VBA module that will convert all text within a Spreadsheet to Proper or Sentence like this ---> Hello Everyone, Hope You Are All Happy.

    Any help or suggestions would be great,

    Thank You in advance,

    Kenny

  2. #2

    Join Date
    Nov 2002
    Posts
    981

    Default Re: Make Text Proper/Sentence Case In VBA Module

    Dim rng As Range, cell As Range
    Set rng = Cells.SpecialCells(xlCellTypeConstants, 2)
    For Each cell In rng
    cell.Value = StrConv(cell.Value, vbProperCase)
    Next

  3. #3
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Crested Butte, CO
    Posts
    26,834

    Default Re: Make Text Proper/Sentence Case In VBA Module

    Here's a neat utility from Ivan F. Moala

    Sub TextCon()
    'code by Ivan
    Dim ocell As Range, ans As String

    ans = Application.InputBox("Type in Letter" & vbCr & _
    "(L)owercase, (U)ppercase, (S)entence, (T)itles ")

    If ans = "" Then Exit Sub

    For Each ocell In Selection.SpecialCells(xlCellTypeConstants, 2)
    Select Case UCase(ans)
    Case "L": ocell = LCase(ocell.Text)
    Case "U": ocell = UCase(ocell.Text)
    Case "S": ocell = UCase(Left(ocell.Text, 1)) & _
    LCase(Right(ocell.Text, Len(ocell.Text) - 1))
    Case "T": ocell = Application.WorksheetFunction.Proper(ocell.Text)
    End Select
    Next

    End Sub

    Hope that helps,

    Smitty

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