Momentary Button to toggle cell Value

[Sorin]

Hello people

I need a button that works like "momentary button" to change the vallue in to a cell: when pressed it should be 1 and when released it should be 0.

Thanks in advance

 

[Legacy 143009]

Place a button on your worksheet. Paste this code in a standard module. Right click to button, assign this macro to the button.

Sub toggle_Value()
  With ActiveSheet
    If .Range("A1").Value = 1 Then
      .Range("A1").Value = 0
    Else
      .Range("A1").Value = 1
    End If
  End With
End Sub

 

[kevin9999]

Not sure what you mean exactly by 'press & release' but if it's simply a button click then I think @Peter_SSs came up with a more succinct methodology recently (assumes your starting value in the cell in question is either 1 or 0/blank)

Sub One_Or_Zero()
    With [A1]
        .Value = 1 - .Value
    End With
End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

This is definitely not an area of strength of mine but this seems to do what I think you are asking.

Put an ActiveX Command Button on your worksheet (presumably near the cell you want the values to appear in - I used cell A1)
Put these code in that worksheet's code module. If you already have other ActiveX Command Buttons on the worksheet you would need to change the name in these codes.

Private Sub CommandButton1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  Range("A1").Value = 1
End Sub

Private Sub CommandButton1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  Range("A1").Value = 0
End Sub

Now when I click & hold the button a 1 appears in A1 and when I release the mouse button the value changes to 0.
Here it is in action

Is that what you wanted?

 

[Sorin]

Welcome to the MrExcel board!

This is definitely not an area of strength of mine but this seems to do what I think you are asking.

Put an ActiveX Command Button on your worksheet (presumably near the cell you want the values to appear in - I used cell A1)
Put these code in that worksheet's code module. If you already have other ActiveX Command Buttons on the worksheet you would need to change the name in these codes.

Private Sub CommandButton1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  Range("A1").Value = 1
End Sub

Private Sub CommandButton1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  Range("A1").Value = 0
End Sub

Now when I click & hold the button a 1 appears in A1 and when I release the mouse button the value changes to 0.
Here it is in action
View attachment 105549

Is that what you wanted?

Place a button on your worksheet. Paste this code in a standard module. Right click to button, assign this macro to the button.

Sub toggle_Value()
  With ActiveSheet
    If .Range("A1").Value = 1 Then
      .Range("A1").Value = 0
    Else
      .Range("A1").Value = 1
    End If
  End With
End Sub

Thak you very much , its work very good as toggle button

 

[Sorin]

Welcome to the MrExcel board!

This is definitely not an area of strength of mine but this seems to do what I think you are asking.

Put an ActiveX Command Button on your worksheet (presumably near the cell you want the values to appear in - I used cell A1)
Put these code in that worksheet's code module. If you already have other ActiveX Command Buttons on the worksheet you would need to change the name in these codes.

Private Sub CommandButton1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  Range("A1").Value = 1
End Sub

Private Sub CommandButton1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  Range("A1").Value = 0
End Sub

Now when I click & hold the button a 1 appears in A1 and when I release the mouse button the value changes to 0.
Here it is in action
View attachment 105549

Is that what you wanted?

It is exactly what I needed, thank you very much

 

[Sorin]

Not sure what you mean exactly by 'press & release' but if it's simply a button click then I think @Peter_SSs came up with a more succinct methodology recently (assumes your starting value in the cell in question is either 1 or 0/blank)

Sub One_Or_Zero()
    With [A1]
        .Value = 1 - .Value
    End With
End Sub

Its work very good as toggle button, Thak you very much

 

[kevin9999]

Its work very good as toggle button, Thak you very much

Glad we could help, and thanks for the feedback

 

[Sorin]

Glad we could help, and thanks for the feedback

 

[Peter_SSs]

It is exactly what I needed, thank you very much

You're welcome. Glad to help.