Combine 2 macros into 1

Giganews Newsgroups
Subject: Combine 2 macros into 1
Posted by:  pub (puiblicstu…@home.com)
Date: Sat, 26 Jul 2008

hi i need to combine 2 macros.
im not the best at macros, but i cut,paste and adjusted this together
i have 2 buttons on my toolbar.
1st button converts all text or selected text to upper or proper case
2nd button converts all text or selected text to lower or sentance case

the problem i have is my MsgBox only had 3 buttons. so i could use the
yes/no with an if else.  i dont know how to add more buttons (im pretty
sure it has something todo with that "vbYesNoCancel" line, and even if i
did, i dont know how to add more if's to attach the macros to the
buttons.

i used to be able to live with 2 separate buttons, but now im limited on
my QAT
hope this makes sense.

Thanks.

heres the 2 macros below.

PROPER CASE AND UPPER CASE

Sub CaseChange()
'
' CaseChange Macro
' Macro recorded 5/6/2007 by pub
'

'
    Dim rAcells As Range, rLoopCells As Range
Dim lReply As Long

    'Set variable to needed cells
    If Selection.Cells.Count = 1 Then
        Set rAcells = ActiveSheet.UsedRange
    Else
      Set rAcells = Selection
    End If

    On Error Resume Next 'In case of NO text constants.
    'Set variable to all text constants
    Set rAcells = rAcells.SpecialCells(xlCellTypeConstants, xlTextValues)

    If rAcells Is Nothing Then
      MsgBox "Could not find any text."
      On Error GoTo 0
      Exit Sub
    End If

    lReply = MsgBox("Select 'Yes' for UPPER CASE or 'No' for Proper
Case.", _
    vbYesNoCancel, "UPPERCASE")
    If lReply = vbCancel Then Exit Sub

    If lReply = vbYes Then ' Convert to Upper Case
          For Each rLoopCells In rAcells
              rLoopCells = StrConv(rLoopCells, vbUpperCase)
          Next rLoopCells
    Else ' Convert to Proper Case
          For Each rLoopCells In rAcells
              rLoopCells = StrConv(rLoopCells, vbProperCase)
          Next rLoopCells
    End If

End Sub

LOWER CASE AND SENTANCE CASE MACRO

Sub Convertcaselowercase()
'
' CaseChange Macro
' Macro recorded 5/6/2007 by pub
'

'
    Dim rAcells As Range, rLoopCells As Range
Dim lReply As Long

    'Set variable to needed cells
    If Selection.Cells.Count = 1 Then
        Set rAcells = ActiveSheet.UsedRange
    Else
      Set rAcells = Selection
    End If

    On Error Resume Next 'In case of NO text constants.
    'Set variable to all text constants
    Set rAcells = rAcells.SpecialCells(xlCellTypeConstants, xlTextValues)

    If rAcells Is Nothing Then
      MsgBox "Could not find any text."
      On Error GoTo 0
      Exit Sub
    End If

    lReply = MsgBox("Select 'Yes' for lower case or 'No' for Sentence
Case.", _
    vbYesNoCancel, "lowercase")
    If lReply = vbCancel Then Exit Sub

    If lReply = vbYes Then ' Convert to lower case
          For Each rLoopCells In rAcells
              rLoopCells = StrConv(rLoopCells, vbLowerCase)
          Next rLoopCells
    Else ' Convert to Proper Case
          For Each cell In Selection.Cells
s = cell.Value
Start = True
For i = 1 To Len(s)
ch = Mid(s, i, 1)
      Select Case ch
      Case "."
      Start = True
      Case "?"
      Start = True
      Case "a" To "z"
      If Start Then ch = UCase(ch): Start = False
      Case "A" To "Z"
      If Start Then Start = False Else ch = LCase(ch)
      End Select
Mid(s, i, 1) = ch
Next
cell.Value = s
Next

    End If

End Sub

Replies