Macro on multiple worksheets: Need summary worksheet to control macros on other sheets

Question

I have a timer macro on multiple, identical worksheets. My users will time their task time and each worksheet represents a different task. I need to have a summary sheet with the macros that start and stop time that is linked to each worksheet so that my users don't have to toggle back and forth between sheets to start the timers for each task. Can you help. Here is the timer code I'm using. It works well on each worksheet, but I don't know how to code the buttons on a summary worksheet to activate this code on a specific worksheet. Here's the code:

 Sub startStopTimer()

If Range("j4") = "Start" Then
    Range("$b$8").Offset(Range("j6") + 1).Value = Now
    Range("j4") = "Stop"
Else
    Range("$b$8").Offset(Range("j6"), 1).Value = Now - Range("$b$8").Offset(Range("j6"))
    Range("$j$4") = "Start"
End If

End Sub

Okay...can't add a picture. But I have 8 data sheets that record the task time for 8 different tasks (the start time and the final duration). That's great, but I need to improve the usability of the tool. So, I would like to have one worksheet with the buttons to start/stop each macro, but I need the macro to still run on the individual worksheets. I hope that's a little clearer. — TWalker

Ambie Ambie · Accepted Answer · 2016-04-13T01:10:00

I'm not sure you need to call your timer routine in each worksheet. You really only need one routine and knowledge of which worksheet to assign the times to.

One way would be with a kind of control panel of buttons on a UserForm. It might look something like this (just 3 worksheets as example):

Then you'd handle all of the click events within the UserForm code. In this example, I've created a collection of Worksheets and each item is accessed by a string key which is the button's name. Skeleton code would be:

Option Explicit
Private Const START_COLOUR As Long = &HFF00&
Private Const START_TEXT As String = "Start"
Private Const STOP_COLOUR As Long = &HFF&
Private Const STOP_TEXT As String = "Stop"
Private mSheets As Collection

Private Sub btnClock1_Click()
    StartStopButton btnClock1
End Sub

Private Sub btnClock2_Click()
    StartStopButton btnClock2
End Sub

Private Sub btnClock3_Click()
    StartStopButton btnClock3
End Sub

Private Sub StartStopButton(btn As CommandButton, Optional initialise As Variant)
    Dim ws As Worksheet
    Dim v As Variant
    Dim startTime As Date

    Set ws = mSheets(btn.Name)
    ws.Activate

    If Not IsMissing(initialise) Then

        'Initialise the button and sheet
        SetProperties btn, CBool(initialise)
        ws.Range("A1").Value = "Not yet actioned"
        ws.Range("B1:D1").ClearContents

    Else

        If btn.BackColor = START_COLOUR Then
            'Set clock running
            SetProperties btn, True
            ws.Range("A1").Value = "Running"
            ws.Range("B1").Value = Now
            ws.Range("C1:D1").ClearContents
        Else
            'Stop clock and calculate difference
            SetProperties btn, False
            ws.Range("A1").Value = "Stopped"
            ws.Range("C1").Value = Now
            v = ws.Range("B1").Value
            If Not IsEmpty(v) And IsDate(v) Then
                'For DateDiff, choose whichever unit you want, I've used seconds ("s")
                ws.Range("D1").Value = DateDiff("s", v, Now)
            End If
        End If

    End If

End Sub
Private Sub SetProperties(btn As CommandButton, running As Boolean)
    With btn
        If running Then
            .Caption = STOP_TEXT
            .BackColor = STOP_COLOUR
        Else
            .Caption = START_TEXT
            .BackColor = START_COLOUR
        End If
    End With

End Sub
Private Sub UserForm_Initialize()
    Dim ws As Worksheet

    'Assign all worksheets to collection
    Set mSheets = New Collection

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    mSheets.Add ws, btnClock1.Name

    Set ws = ThisWorkbook.Worksheets("Sheet2")
    mSheets.Add ws, btnClock2.Name

    Set ws = ThisWorkbook.Worksheets("Sheet3")
    mSheets.Add ws, btnClock3.Name

    'Set all buttons to start
    StartStopButton btnClock1, False
    StartStopButton btnClock2, False
    StartStopButton btnClock3, False

End Sub

Macro on multiple worksheets: Need summary worksheet to control macros on other sheets

1 Answers