Variable Text on a Button
 

For most macros I make, I use a Button, from the Forms taskbar to execute it.

What I want to do is set up a Button that runs 2 macro's, depending on what
was last run. I've done this easily before by setting both scripts in the
same macro, and a simple IF statement to look at a cell that is change at the
end of each macro. An example is it changes from "Automatic" to "Manual" and
this removes or adds forumlas in certain cells.

My question is, can I change the text on the button dependant on cells? So
if A1 is 1, the button says "Automatic" and if A1 is 0 the button reads as
"Manual" ?

Variable Text on a Button
 

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "A1"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value = 1 Then
Me.Buttons("Button 2").Caption = "Automatic"
Else
Me.Buttons("Button 2").Caption = "Manual"
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"PaulW" wrote in message
...
For most macros I make, I use a Button, from the Forms taskbar to execute
it.

What I want to do is set up a Button that runs 2 macro's, depending on
what
was last run. I've done this easily before by setting both scripts in the
same macro, and a simple IF statement to look at a cell that is change at
the
end of each macro. An example is it changes from "Automatic" to "Manual"
and
this removes or adds forumlas in certain cells.

My question is, can I change the text on the button dependant on cells? So
if A1 is 1, the button says "Automatic" and if A1 is 0 the button reads as
"Manual" ?