Timer macro starting on wrong sheet in workbook

[basic_coder]

Hello all,

I currently have an excel workbook that runs two independent timers on it. Both are very simple: they start from 0 and count up until stopped or reset.
My problem is that this workbook will have one new worksheet for every day of the month, created on that day. The timer only works on day 1 of the month. Once new sheets are created, the timer will only start on the first sheet, even if the button is pressed on Sheet 2 or Sheet 3. I have tried changing the wording to make it refer to the active sheet and similar things, but I can't make it work. Over time, every single sheet will have to have a timer built into it, but only the timer on the active sheet should start counting up when clicked. Right now, as stated, it starts the timer on the very first sheet no matter which sheet I clicked "Start" on. Here is the code I have:

Please Login or Register  to view this content.

I understand that my code at the moment is referencing the sheet named "Template" which is always the first sheet of the month. Every other sheet after that has code that names it based on the current date. I haven't been able to figure out how to make this work. If I change it to "ActiveSheet.NextTick", or "Me.NextTick", or "ThisWorkbook.NextTick", all of them cause it to fail. I just want the timer to activate on the current sheet.

I would love some assistance if anyone has ideas!


Thanks in advance!

[Bernie Deitrick]

The danger with Activesheet is that it can change if the user clicks on another tab. So, copy this and place it in a standard codemodule, and use forms buttons to which you assign the macros.


This will only work on the last sheet of the workbook - if that is not what you want, take out these lines:

If ActiveSheet.Index <> Sheets.Count Then
MsgBox "Only click the button on the last sheet"
Exit Sub
End If

Please Login or Register  to view this content.

[basic_coder]

That worked beautifully, thank you!
Out of curiosity, is there any particular reason in this case that you opted to have the code in a module as opposed to private subs within the worksheet? I tend to prefer ActiveX controls that refer to individual code in the sheet due to the design of the sheets. However, this isn't too big a deal to work with anyway (especially since you finally got it working for me!), so I am more or less just curious on the pro's and con's of doing it either way.

Thanks again for the help!

[Bernie Deitrick]

The disadvantage (in my opinion, for your situation) is that using ActiveX controls with their code in the codemodule of the sheet is that the code gets replicated every time the sheet gets copied. So then you have a harder time when you want to make changes - are you editing the code of the template, or the first sheet , or the last sheet.... Using the assigned macro method means code only appears in one place, so it is easier to track. You could use a similar method with ActiveX code, but instead of using Activesheet you would be better off using the Me object in its place.

[basic_coder]

That makes sense, and you are right that it is easier to track this way. I'll keep it as you've written for simplicity's sake.

One last question then since I have you here. I am exploring the option of potentially having several more timers in the future. However, due to limited visible space (don't want to have users do too much scrolling around), I can only fit two on the page at a time. Is it possible to have, for example, half a dozen timers running in a UserForm wherein the user can simply click a "timers" button to open a UserForm window that shows all 6 timers, and each timer can be started/stopped/reset independently? This would also require live updates of the timers so the user doesn't have to keep opening/closing the UserForm to see the current timer status.

Essentially just a pop-up window that shows these timers instead of having them all built into the excel sheet itself. If this is too complex, the two timers will suffice for now. It would be nice to explore the potential of more timers for more independent events.

Thanks!

[basic_coder]

The danger with Activesheet is that it can change if the user clicks on another tab. So, copy this and place it in a standard codemodule, and use forms buttons to which you assign the macros.


This will only work on the last sheet of the workbook - if that is not what you want, take out these lines:

If ActiveSheet.Index <> Sheets.Count Then
MsgBox "Only click the button on the last sheet"
Exit Sub
End If

Please Login or Register  to view this content.

I've spoken too soon. There is another minor problem.

The way a new sheet is created at the start of the day for every day of the month is by using a separate Excel workbook that contains the template. Basically, the current Monthly workbook will open up the Template workbook, copy the template sheet, and paste it into the Monthly workbook, and rename the sheet to the current day.

The problem is that when I create a new sheet for the day, the Timer now has to open up the Template workbook and then it starts the timer. I presume this is because the code is now referencing the Module within my Template workbook rather than the same module contained within the Monthly workbook.

I hope that makes sense. Any ideas on a solution?

Thanks!

[Bernie Deitrick]

There is no reason not the have more than two on a sheet - the buttons can be really small.

The userform idea is do-able, though you would probably want to load it modelessly to allow it to stay visible while the sheet is still available.