How to alternate between 2 numbers in a cell in XL2000

[byudave]

This probably sounds simple, but I haven't been able to figure it out. I'm trying to make a cell alternate between 0 and 1, or 1 and 2, etc. every time you enter in new data or press DELETE. I don't care what numbers or letters it alternates between, but I need it to switch back and forth every time. I've tried the functions RAND and RANDBETWEEN, but those only work part of the time since they're random.

Here's what I'm doing: I'm making a spreadsheet for my class to simulate flashcards where it will randomly cycle through a bank of fact I've put together so we can study better. I'd prefer that the "back" of the flashcard isn't visible until the student presses a button, which is where my problem comes in. Every time they press DELETE, the cell randomly displays a new fact, but if I could alternate a cell between two numbers, it could change the displayed question when teh value is one number and display the answer when the cell displays the other number. Maybe it still wouldn't even work if I had this function. Any ideas?

 

[dafan]

This will alternate A1 between 1 and 2 if Delete is pressed (or anything else is changed):

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Range("A1").Value = 1 Then Range("A1").Value = 2 Else Range("A1").Value = 1
Application.EnableEvents = True
End Sub

 

[Peter_SSs]

byudave

Welcome to the MrExcel board!

We may need to know a bit more about what is going on in your sheet, but this code switches cell A1 between 0 and -1 evry time there is a chnage on the sheet. To implement it..
1. Right click the sheet name tab and choose "View Code"
2. Copy and Paste the code below into the main right hand pane that opens at step 1. (Adjust the "A1" to whatever cell you want to alternate)
3. Close the VB window
4. Make changes on your sheet.

Post back with more details if this does not meet your need.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>    Range("A1").Value = <SPAN style="color:#00007F">Not</SPAN> Range("A1").Value<br>    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[byudave]

That sounds perfect. One other question, though - where do I enter in that code? I've always done everything I need with complicated IF/THEN, INDIRECT, etc. references and never bothered with codes or macros.

 

[byudave]

Ah. Thanks.

 

[byudave]

Ok. Almost there - one more thing should do the trick. I have a number in cell D10 that is generated using =IF(B16>0,B16,IF(B17>0,B17,B18)) where B16 and B17 are non-zero and random if the user enters data in a field, but are otherwise zero, and B18 always displays a random number. Now that A1 goes from 0 to -1 every time anything changes in the sheet, how can I make my D10 cell follow its IF/THEN statement and retrieve a new number when A1=0, but stay with the previous random number if A1=-1? This should solve my problem completely.

 

[byudave]

FYI, all of column A is open except A1 in case any of you have suggestions that require some empty cells.

 

[Gunswick]

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Range("A1").Value = Not Range("A1").Value
Application.EnableEvents = True

If ("A1") = 0 Then
calculation.automatic = true

else

calculation.automatic = False
End If

End Sub

<!-- / message --><!-- sig -->

 

[byudave]

I kind of solved my own problem, with inspiration from people who offered suggestions. Instead of having a macro that switches A1 from 0 to -1 and basing my self-referencing cell off of that one, I did something else. Here's what I did:

A3=1
A4=2
A5=A3-A4+A6
A6=A3+A4-A5

I set this to allow iterations with a maximum number of iterations=1. As this is set up, A5 and A6 will aternate between A5=3 and A6=0, and A5=-1 and A6=4. Instead of setting up my equation to see if A1=0 or -1, I set it to look if A5>A6 or A6>A5. I then entered in a formula in G17 where

G17=IF(A5>A6,G15,G17)

where G15 was a random number. When A5>A6, it gives me a random number, but when A6>A5, it gives me the same random number without changing. Thanks to all who gave me suggestions! My spreadsheet works perfectly!!!