Button to toggle cell Value

[neilsinc]

I have a button on my sheet that I want to use to toggle a cell value between 1 and 0.

Any idea how I would do that with VBA?

i.e. if the value is 1 when the button is pressed, change it to 0 and vice versa

Thanks in advance

 

[MickG]

Try:-

Private Sub CommandButton1_Click()
With Range("A1")
    .Value = IIf(.Value = 1, 0, 1)
End With
End Sub

 

[My Aswer Is This]

Assuming we are toggling the value in Range ("A1") then use this script:

Sub Add_Value()
    If Range("A1").Value ="1" Then
        Range("A1").Value = "0"
            Else: Range("A1").Value = "1"
    End If
End Sub

 

[Rick Rothstein]

Try:-

Private Sub CommandButton1_Click()
With Range("A1")
    .Value = IIf(.Value = 1, 0, 1)
End With
End Sub

You can simplify while eliminating the slow IIf function at the same time...

Private Sub CommandButton1_Click()
  With Range("A1")
    .Value = 1 - .Value
  End With
End Sub

Although I would probably eliminate the With..End With block (not sure anything is gained by using it when there is only 2 references to its object)...

Private Sub CommandButton1_Click()
  Range("A1").Value = 1 - Range("A1").Value
End Sub

 

[neilsinc]

Perfect. Thanks everyone

 

[My Aswer Is This]

If we are only toggling the value in one cell I do not see why speed needs to be considered.

You can simplify while eliminating the slow IIf function at the same time...

Private Sub CommandButton1_Click()
  With Range("A1")
    .Value = 1 - .Value
  End With
End Sub

Although I would probably eliminate the With..End With block (not sure anything is gained by using it when there is only 2 references to its object)...

Private Sub CommandButton1_Click()
  Range("A1").Value = 1 - Range("A1").Value
End Sub