Alternate Button Macro & Caption Between 2

Jaffey · Apr 23rd 2008

I have a worksheet with 2 buttons labelled "Hide" and "Show". As the names imply, they allow the user to hide or show parts of the worksheet. I would like to combine them into one button and have the button label and the associated macro change with each press of the button. Here is what I have so far;

Code

Sub SHOW_LEADS()
    Rows("10:15").Select
    Selection.EntireRow.Hidden = False
    ActiveSheet.Shapes("Button 20").Select
    Selection.Characters.Text = "HIDE LEADS"
    Range("A1").Select 'is there a better way to remove the focus from the button than selecting a cell off the button?
End Sub


Sub HIDE_LEADS()
    Rows("10:14").Select
    Selection.EntireRow.Hidden = True
    ActiveSheet.Shapes("Button 20").Select
    Selection.Characters.Text = "SHOW LEADS"
    Range("A1").Select
End Sub

Display More

These macros change the label fine after hiding or unhding the rows but I can't find the proper terms to use to change the macro associated with the button (if there is one?)
As always, your help is greatly appreciated!

Simon Lloyd · Apr 23rd 2008

Re: Alternate Between 2 Macros With One Button

Try something like this:

Code

Option Explicit 
Public s As Integer ''''This is a publicly declared Integer i.e the value of s can be called upon elsewhere
Private Sub CommandButton1_Click() 
     
    If s = 0 Then 
        MsgBox "Hi" '''Substitute this for first code to run
        CommandButton1.Caption = "Ready to Hide" 
        s = 1 
    ElseIf s = 1 Then 
        MsgBox "Bye" '''''substitute this for second code
        s = 0 
        CommandButton1.Caption = "Ready to Show" 
    End If 
End Sub

Display More

AAE · Apr 23rd 2008

Re: Alternate Between 2 Macros With One Button

Nice job Simon. :razz: (BTW - "Option Explicit" got left out of the code tags)

Just to state what may not be obvious to the OP - to use this code, you must use it with a button created from the Control Toolbox, not a button created from the Forms Toolbar.

Simon Lloyd · Apr 23rd 2008

Re: Alternate Between 2 Macros With One Button

Quote from AAE

Nice job Simon. (BTW - "Option Explicit" got left out of the code tags)

Thanks and fixed, as for the "Button" i should have stated that but there again the user can adapt the idea of the code to their particular button!

Jaffey · Apr 29th 2008

Re: Alternate Button Macro & Caption Between 2

Thank you very much Simon...this works perfect!

Alternate Button Macro & Caption Between 2

Participate now!

Share