When Cell equals value, change cell value

lonlyboy

New Member
Joined
Feb 16, 2010
Messages
23
I thought this seemed easy.
First; I'm running Win XP using Excel 2007.

I'm creating a very simple excel sheet to help my son learn to count.
I have 2 buttons, one labeled "add" and one "sub".

My son is learning his numbers from 1-31 right now.
I have the following for "add" and similiar for "sub".

Code:
Sub Addition()
Range("A1").Value = Range("A1").Value + 1
End Sub

bascially what I want to do is when my son gets to 31 and hits "add" again I want excel to notice the next number is 32 and reset cell A1 to "0".

Also looking for the reverse. If my son hits "sub" and goes below "0" it keeps reseting to "0". That way my son only sees numbers 0-31.

I've tried this under the worksheet:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "A1" And Range("A1").Value = -1 Then Run "Zero"
If Target.Address = "A1" And Range("A1").Value = 32 Then Run "Zero"
End Sub

Excel does nothing with this code.
Any thoughts would be helpful.

LB
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

repairman615

Well-known Member
Joined
Dec 21, 2009
Messages
1,885
Hello,

Perhaps this could help.

Code:
If Range("A1").Value + 1 <> 32 Then
    Range("A1").Value = Range("A1").Value + 1
Else
    Range("A1").Value = 0
End If
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Or, along similar lines

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Addition()<br>    <SPAN style="color:#00007F">Dim</SPAN> Num <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> myLimit <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 31 <SPAN style="color:#007F00">'<- change to suit</SPAN><br>    Num = Range("A1").Value<br>    Range("A1").Value = IIf(Num = myLimit, 0, Num + 1)<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 

lonlyboy

New Member
Joined
Feb 16, 2010
Messages
23
repairman,
for some reason when using your code to count up it worked fine, but couldn't get it to work once it went below zero.

Thanks for trying.

Peter,
Your code seems to work beautifully. I created a large sized A1 cell and have spent almost an hour with my son going through numbers. Now he can flip through them on his own and stay within the numbers they are teaching at school.
Thanks again..

LB
 

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,329
Members
414,445
Latest member
walramgo02

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top