Execute Cell with a Macro (timer)

**flds** · 05-05-2010, 09:44 PM

Hi
I am learning guitar and I felt that if I use random chord names by using a timer say 10 seconds to auto change the chord in Excel, I could increase my speed of changing chords. I have list of chords in A1:A17 and this formula =INDEX(A1:A17,RANDBETWEEN(1,COUNTA(A1:A17))) entered in C1.
What I want is a Macro with a timer to run the above formula every 10 seconds and auto change the chord in cell C1. The macro to include Start and Stop button, if possible.
Let me know where to insert the macro in the workbook.
Any help would be appreciated.
Thanks
FLDS

Note: I have posted this same request in Mr Excel forum, NO replies

**mdbct** · 05-05-2010, 11:20 PM

In Sheet1's module the folllowing is assigned to a button called StartStop

Please Login or Register  to view this content.

In a standard module:

Please Login or Register  to view this content.

I've attached a sample book.

**flds** · 05-11-2010, 07:45 PM

Hi Mdbct,
Thank you for your time to help me. This is exactly what I wanted, But your added feature of the count in column 5 was great. I did not think of this.

I have a small request, is it possbile to have an input box on sheet1 to enter the time (i.e. seconds value 10), [Application.OnTime Now() + TimeSerial(0, 0, 10), "refreshIt" ]
Instead of going into the code and changing it.
As I improve my playing speed, I would like to decrease the time.

I hope, I have explained this well.

Thanks once again, I truely appreciate your help.

FLDS

**mdbct** · 05-11-2010, 09:05 PM

I'm glad you liked my unintentional added feature. It was left over from my testing - I had meant to remove that part.

I've attached an updated version that uses a textbox to vary the interval. For ease of use (less clicking), I would considered using a designated cell (E5 in this sheet's case - the yellow one on the sheet) for entering the variable time.

The new code:

Please Login or Register  to view this content.

I've highlighted (in bold but it's hard to tell) the two lines for the assigning of the variable time. Currently the textbox controls the time while the other line is commented out

**flds** · 05-12-2010, 09:20 PM

Hi Mdbct,

Thanks for your reply, appreciate your help.

I have attached the file which I modified to look a bit impressive. (your comments)

1. One issue on this file is, in the filed "Enter Interval Time Here" when I leave this blank or type a 0 (zero) it continuously runs and cannot be stopped until I press Esc and a "end,Debug window pops up. It starts again.

2. I am unable to protect the file (sheet) to save it from misuse.

If you are able to fix this it would be great.

I am happy and I love this program you created for me. Thanks so much.

FLDS

**mdbct** · 05-12-2010, 11:14 PM

Changes:
1) Sheet is now protected - password is your user name (flds)
2) Interval textbox must be a number (greater than zero) and not a blank entry
3) When the button is clicked, the sheet is unprotected at each interval, the changes occur and the sheet is re-protected.
4)"Reset this Counter" is now a transparent button, click on it to reset the counter instead of clearing the cell (which you cant do since the sheet is protected).

**flds** · 05-13-2010, 07:26 AM

Hi mdbct,

Thanks for the fix. You did what I did not expect in this program, this is wonderful, I hope some day I will be able to play my guitar.

Thanks once again.

FLDS