Store cell value and set to zero
 

The question is a 'set to zero' and 'reset to the previous' problem

I have a cell with anything inside (say A1=23). With a set button I want to
change the value of A1 to 0, and then repushing the button I want to get back
23 in A1. Is it possible? And what if there is a formula in the cell?

all my best..

Store cell value and set to zero
 

First enter the following macro into a standard module:

Public storeIt As Variant
Sub flipFlop()
With Range("A1")
If .Value = 0 Then
.Value = storeIt
Else
storeIt = .Value
.Value = 0
End If
End With
End Sub

Then create a button in the usual way and assign the macro to it.
--
Gary''s Student - gsnu200778


"noyau" wrote:

The question is a 'set to zero' and 'reset to the previous' problem

I have a cell with anything inside (say A1=23). With a set button I want to
change the value of A1 to 0, and then repushing the button I want to get back
23 in A1. Is it possible? And what if there is a formula in the cell?

all my best..

Store cell value and set to zero
 

Unfortunately I was enable to call back the previous value.


The program that I use for the button is the following one; but as you see
there is only 0 and a fixed (unchangeable) value to call back


Private Sub CommandButton2_Click()
If CommandButton2.Caption = "Running" Then
Range("a1").Value = 0
CommandButton2.Caption = "Shutdown"
CommandButton2.BackColor = &HFF&
'Range("b2;b3").Copy Destination: Range ("b4")
Else
Range("a1").Value = 5.4
CommandButton2.Caption = "Running"
CommandButton2.BackColor = &HFF00&
End If
End Sub

Store cell value and set to zero
 

We are making excellent progress!

Calling back the value that was in A1 requires storing it in a public,
static, location. If you use a variable in the macro, VBA "forgets" its
value each time the button is pressed. Therefore, from Excel:


1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste in the following single line:
Public flipflop as Variant
4. lose the VBE window


Then update your code as follows:

Private Sub CommandButton2_Click()
If CommandButton2.Caption = "Running" Then
flipflop = Range("a1").value
Range("a1").Value = 0
CommandButton2.Caption = "Shutdown"
CommandButton2.BackColor = &HFF&
'Range("b2;b3").Copy Destination: Range ("b4")
Else
Range("a1").Value = flipflop
CommandButton2.Caption = "Running"
CommandButton2.BackColor = &HFF00&
End If
End Sub




--
Gary''s Student - gsnu200778


"noyau" wrote:


Unfortunately I was enable to call back the previous value.


The program that I use for the button is the following one; but as you see
there is only 0 and a fixed (unchangeable) value to call back


Private Sub CommandButton2_Click()
If CommandButton2.Caption = "Running" Then
Range("a1").Value = 0
CommandButton2.Caption = "Shutdown"
CommandButton2.BackColor = &HFF&
'Range("b2;b3").Copy Destination: Range ("b4")
Else
Range("a1").Value = 5.4
CommandButton2.Caption = "Running"
CommandButton2.BackColor = &HFF00&
End If
End Sub