How do I set the cell value to 0
 

I want the cell to change back to 0 after I type in a number and hit enter, I
have a formula in another cell that uses the number I enter, but if the cell
does not return to zero directly after I hit enter, it will keep adding
whatever number is in the cell. Please Help!

How do I set the cell value to 0
 

This tiny macro does what you want to cell A1, You can adapt it to any cell.

Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range("A1"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Value = 0
Application.EnableEvents = True
End Sub

Remember that this is worksheet code. Right click on the tab, select view
code and paste the stuff in.
--
Gary's Student


"GoodTrouble" wrote:

I want the cell to change back to 0 after I type in a number and hit enter, I
have a formula in another cell that uses the number I enter, but if the cell
does not return to zero directly after I hit enter, it will keep adding
whatever number is in the cell. Please Help!

How do I set the cell value to 0
 

Ok, I see where to put the code in, but how do I adapt it?? I have many
groups of cells that it needs to work for...
For Example:
C9:C12
C16:C19
H2:H5
H9:H12
H16:H19
Each cell in those ranges has a zero....I need to type a number into it, the
formula from the other cells in the sheet will pull that number, and then I
need this code to erase the number I just typed in and set the cell back to
zero...

"Gary''s Student" wrote:

This tiny macro does what you want to cell A1, You can adapt it to any cell.

Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range("A1"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Value = 0
Application.EnableEvents = True
End Sub

Remember that this is worksheet code. Right click on the tab, select view
code and paste the stuff in.
--
Gary's Student


"GoodTrouble" wrote:

I want the cell to change back to 0 after I type in a number and hit enter, I
have a formula in another cell that uses the number I enter, but if the cell
does not return to zero directly after I hit enter, it will keep adding
whatever number is in the cell. Please Help!

How do I set the cell value to 0
 

Ok nevermind!! I got it, I just put the cell ranges that I just typed in
there into the code!! Duh! I should have known that! But how can I get it to
work with all the other ranges that I listed, I can only get it to work for
one range...do I just use a comma in between each range??
"Gary''s Student" wrote:

This tiny macro does what you want to cell A1, You can adapt it to any cell.

Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range("A1"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Value = 0
Application.EnableEvents = True
End Sub

Remember that this is worksheet code. Right click on the tab, select view
code and paste the stuff in.
--
Gary's Student


"GoodTrouble" wrote:

I want the cell to change back to 0 after I type in a number and hit enter, I
have a formula in another cell that uses the number I enter, but if the cell
does not return to zero directly after I hit enter, it will keep adding
whatever number is in the cell. Please Help!

How do I set the cell value to 0
 

This line:
If Intersect(Range("A1"), Target) Is Nothing Then Exit Sub
becomes:
If Intersect(Range("c9:c12,c16:c19,h2:h5,h9:h12,h16:h 19"), Target) _
Is Nothing Then Exit Sub



GoodTrouble wrote:

Ok, I see where to put the code in, but how do I adapt it?? I have many
groups of cells that it needs to work for...
For Example:
C9:C12
C16:C19
H2:H5
H9:H12
H16:H19
Each cell in those ranges has a zero....I need to type a number into it, the
formula from the other cells in the sheet will pull that number, and then I
need this code to erase the number I just typed in and set the cell back to
zero...

"Gary''s Student" wrote:

This tiny macro does what you want to cell A1, You can adapt it to any cell.

Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range("A1"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Value = 0
Application.EnableEvents = True
End Sub

Remember that this is worksheet code. Right click on the tab, select view
code and paste the stuff in.
--
Gary's Student


"GoodTrouble" wrote:

I want the cell to change back to 0 after I type in a number and hit enter, I
have a formula in another cell that uses the number I enter, but if the cell
does not return to zero directly after I hit enter, it will keep adding
whatever number is in the cell. Please Help!

--

Dave Peterson

How do I set the cell value to 0
 

Alrighty, working perfectly now! Thank You so much!

"GoodTrouble" wrote:

Ok nevermind!! I got it, I just put the cell ranges that I just typed in
there into the code!! Duh! I should have known that! But how can I get it to
work with all the other ranges that I listed, I can only get it to work for
one range...do I just use a comma in between each range??
"Gary''s Student" wrote:

This tiny macro does what you want to cell A1, You can adapt it to any cell.

Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range("A1"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Value = 0
Application.EnableEvents = True
End Sub

Remember that this is worksheet code. Right click on the tab, select view
code and paste the stuff in.
--
Gary's Student


"GoodTrouble" wrote:

I want the cell to change back to 0 after I type in a number and hit enter, I
have a formula in another cell that uses the number I enter, but if the cell
does not return to zero directly after I hit enter, it will keep adding
whatever number is in the cell. Please Help!