Macro to change cell String

[Papi]

The code is written to change the category in column R of the active row. There are three options and three macros written as below. They are CURRENT, PAID and REVIEW. It it possible to run the same under one macro and have it cycle thru each time the macro button is clicked? If it was CURRENT then it would change to PAID and if done again to REVIEW, and again to REVIEW etc?

Private Sub CommandButton20_Click()
' CHANGE TO PAID

    Select Case ActiveCell.Row
    Case 1 To 5
        MsgBox "PAID macro will not work on row " & ActiveCell.Row & ".  Please try again!",
Exit Sub
End Select
    Range("R" & ActiveCell.Row).Value = "PAID"
    Range("S" & ActiveCell.Row).Select

End Sub

 

[My Aswer Is This]

Try this:
Supplied by VOG earlier.

Sub BigMacro
Call Macro1
Call Macro2
Call Macro3
'etc
End Sub

 

[Papi]

Thanks for taking the time to respond. That does not work as it scrolls all the way through without stopping and finishes with Macro3 each time. The concept is close but it needs to stop after running the first one with the user being able to run it one more time or more.

 

[Rick Rothstein]

See if this code does what you want...

Sub CommandButton20_Click()
  Dim FirstWord As String, Cat() As String
  If ActiveCell.Row < 6 Then
    MsgBox "PAID macro will not work on row " & ActiveCell.Row & ".  Please try again!"
  Else
    FirstWord = Split(Trim(Range("R" & ActiveCell.Row).Value & " REVIEW"))(0)
    Range("R" & ActiveCell.Row).Value = Split(Trim(Split("CURRENT PAID REVIEW CURRENT", FirstWord)(1)))(0)
    Range("S" & ActiveCell.Row).Select
  End If
End Sub

 

[Papi]

Hello Rick,

Thanks so much. You always amaze so many people with your superior skills. That is smack on and works like a charm. Again thanks!