|  | Combine 2 macros into 1 | | |
| | | pub |  |
|  Posted: Sat Jul 26, 2008 1:34 pm Post subject: Combine 2 macros into 1 |  |
| |  | |
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 |
| |
| | | Don Guillett | |
| | Posted: Sat Jul 26, 2008 1:45 pm Post subject: Re: Combine 2 macros into 1 | |
| | | |
Try this simple idea
Sub whichbox()
x = UCase(InputBox("Enter U for Upper or L for lower"))
If x = "U" Then
MsgBox "upper"
Else
MsgBox "lower"
End If
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"pub" <puiblicstuff@home.com> wrote in message
news:QtHik.3836$KZ.50@newsfe03.iad...
| Quote: | 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 |
|
| |
| | | pub | |
| | Posted: Sat Jul 26, 2008 4:27 pm Post subject: Re: Combine 2 macros into 1 | |
"Don Guillett" <dguillett1@austin.rr.com> wrote in news:usVN0az7IHA.2544
@TK2MSFTNGP04.phx.gbl:
| Quote: | Try this simple idea
Sub whichbox()
x = UCase(InputBox("Enter U for Upper or L for lower"))
If x = "U" Then
MsgBox "upper"
Else
MsgBox "lower"
End If
End Sub
|
lol, why didnt i think of that?
i used a MsgBox to put 2 macros together into 1 button, but i never
considered using a MsgBox to put 2 macros together into 1 button. at least
it makes sense to me.
Thanks! |
| |
|
|
