Command Button - Auto run

[dcaraher]

I have a command button with a click counter in the caption.

I am using it to recalculate a sheet which contains two random generated numbers between 0 and 36.

I would like to add code which runs the event on click until both the random generated numbers match. Statistically this has odds of over 1,300 -1 so I want to avoid having to click the button 1,300+ times.

Thanks for any advice.

 

[dcaraher]

This looks like it should be a loop until statement but not sure how to make it work.

My code so far is:

Private Sub CommandButton1_Click()
Sheet1.Calculate
Static cnt As Long
 cnt = cnt + 1
 Me.CommandButton1.Caption = "I have been clicked " & cnt & " times"
End Sub

Any help appreciated

 

[dcaraher]

I think I have solved the initial problem. Not the most elegant but it works. I created a helper cell which used an if statement with the result if the two cells matched it said "STOP"

I then used the following code:

Private Sub CommandButton1_Click()
Do Until Range("D2").Value = "STOP"
Sheet1.Calculate
Static cnt As Long
 cnt = cnt + 1
 Me.CommandButton1.Caption = "I have been clicked " & cnt & " times"
 Loop
 End Sub

My new problem is how can I reset the counter without closing the workbook?

 

[Smitty]

Welcome to the Board!

My new problem is how can I reset the counter without closing the workbook?

Why not just add Range("D2").Value = "" at the beginning of the code?

 

[dcaraher]

Thanks very much for the response.

I have tried adding at the beginning of the code and I end up with a continuous loop. It is obviously me doing something wrong.

What I'm trying to achieve is let the code run, get the number of "clicks" from the counter, then reset the counter to zero

Where should I place the code.

Thanks again

 

[Smitty]

The code will keep going until your Stop condition is met. Does it ever get to "Stop"?

 

[dcaraher]

Yes when the two random generated numbers match, the IF statement shows "STOP" and the code stops and gives me the number of clicks.

I am unable to reset the counter without closing and saving the workbook.

Thanks

 

[Smitty]

If you add this:

Range("D2").Value = ""

What happens when you step through the code?