Hi,

I have a cell which has a defined name "status". It currently displays the
word, "Off". On clicking a button I want it to toggle from "Off" to "On"
and vice versa. Any help is much appreciated.

TIA
Tom

Tom wrote:
Hi,

I have a cell which has a defined name "status". It currently displays the
word, "Off". On clicking a button I want it to toggle from "Off" to "On"
and vice versa. Any help is much appreciated.

TIA
Tom



Add a VB command button to the worksheet (accessible from the Visual
Basic toolbar's toolbox). (This is not the same as a Form button -- you
can use that as well but the steps are a little different.)

With the worksheet in design mode (should be already), double click the
new button to reveal the empty sub:

Private Sub CommandButton1_Click()

Add this code:

Private Sub CommandButton1_Click()
If Range("Status").Value = "On" Then
Range("Status").Value = "Off"
ElseIf Range("Status").Value = "Off" Then
Range("Status").Value = "On"
End If
End Sub

Return to the worksheet, turn off design mode (the triangle/ruler/pencil
widget) and try out your new button.

That was great. Thanks smartin.

"smartin" wrote in message
...
Tom wrote:
Hi,

I have a cell which has a defined name "status". It currently displays
the word, "Off". On clicking a button I want it to toggle from "Off" to
"On"
and vice versa. Any help is much appreciated.

TIA
Tom

Add a VB command button to the worksheet (accessible from the Visual Basic
toolbar's toolbox). (This is not the same as a Form button -- you can use
that as well but the steps are a little different.)

With the worksheet in design mode (should be already), double click the
new button to reveal the empty sub:

Private Sub CommandButton1_Click()

Add this code:

Private Sub CommandButton1_Click()
If Range("Status").Value = "On" Then
Range("Status").Value = "Off"
ElseIf Range("Status").Value = "Off" Then
Range("Status").Value = "On"
End If
End Sub

Return to the worksheet, turn off design mode (the triangle/ruler/pencil
widget) and try out your new button.

I've just moved the macro from the Personal worksheet to its own worksheet
and attached it to a button. Now it has stopped working. Weird. Any
suggestion how I can get it to work again?

Tom

"smartin" wrote in message
...
Tom wrote:
Hi,

I have a cell which has a defined name "status". It currently displays
the word, "Off". On clicking a button I want it to toggle from "Off" to
"On"
and vice versa. Any help is much appreciated.

TIA
Tom

Add a VB command button to the worksheet (accessible from the Visual Basic
toolbar's toolbox). (This is not the same as a Form button -- you can use
that as well but the steps are a little different.)

With the worksheet in design mode (should be already), double click the
new button to reveal the empty sub:

Private Sub CommandButton1_Click()

Add this code:

Private Sub CommandButton1_Click()
If Range("Status").Value = "On" Then
Range("Status").Value = "Off"
ElseIf Range("Status").Value = "Off" Then
Range("Status").Value = "On"
End If
End Sub

Return to the worksheet, turn off design mode (the triangle/ruler/pencil
widget) and try out your new button.