VBA needs to identify a newly formatted table

[TashaBirchall]

I've set up a stopwatch in excel using a VBA code which I found on YouTube. The excel worksheet where the stopwatch function is used is currently not within an excel table and the VBA code just points to a range of cells.

I now need to format the excel worksheet using the stopwatch into a table for various reasons! However, when I simply reformat my selected cell range into a table, it breaks the stopwatch function. Because I don't understand VBA very well, I am struggling to identify what changes I need to make to the VBA so that it identifies the table and works. I have tried setting 'table1' as an object and pointing the code toward 'table1' but it isn't working.

Does anyone know what I need to do? Or know what changes I need to make to the VBA? Thank you in advance.

VBA code used;

Option Explicit

Sub Initialize()

Dim iRow As Long

iRow = Sheets("T&M").Range("H" & Application.Rows.Count).End(xlUp).Row + 1

If Sheets("T&M").Range("F" & iRow).Value = "" Then

Sheets("T&M").Range("B" & iRow).Value = [Today()]

End If

End Sub

Sub Start_Time()

Dim iRow As Long

iRow = Sheets("T&M").Range("H" & Application.Rows.Count).End(xlUp).Row + 1

If Sheets("T&M").Range("F" & iRow).Value <> "" Then

MsgBox "Start Time is already captured for the selected task", vbOKOnly + vbInformation, "Start Time Available"
Exit Sub

Else

Sheets("T&M").Range("F" & iRow).Value = [Now()]

Sheets("T&M").Range("F" & iRow).NumberFormat = "hh:mm:ss AM/PM"

End If

End Sub

Sub End_Time()

Dim iRow As Long

iRow = Sheets("T&M").Range("H" & Application.Rows.Count).End(xlUp).Row + 1

If Sheets("T&M").Range("F" & iRow).Value = "" Then

MsgBox "Start Time has not been captured for this task.", vbOKOnly + vbInformation, "Start Time Pending"
Exit Sub
Else

Sheets("T&M").Range("G" & iRow).Value = [Now()]
Sheets("T&M").Range("G" & iRow).NumberFormat = "hh:mm:ss AM/PM"

Sheets("T&M").Range("H" & iRow).Value = Sheets("T&M").Range("G" & iRow).Value - Sheets("T&M").Range("F" & iRow).Value

Sheets("T&M").Range("H" & iRow).NumberFormat = "hh:mm:ss"

End If

Call Initialize

End Sub

[#2]

@TashaBirchall 

Since you say "I simply reformat my selected cell range into a table", the result is a "range table".  To verify this, you can execute (e.g., in the Immediate window):

Debug.Print ActiveSheet.ListObjects("Table1").SourceType

and you will see a value of 1.  (You can view the list of other possible table source types in the Object Browser; it's the enumeration XlListObjectSourceType.)  I don't know if my answers will work for other table source types.

Your code shows column letters, but does not mention the table's column names, which are important when working with tables.  I put together a T&M spreadsheet that contains some data in a worksheet range, and other similar data in a table named Table1.  I made a guess at column names (and in one case, the type of column content as well), and where I did not include a name, Excel assigned them:

So for my table, the Start Time is recorded when a Task (name/description) is entered, and the End Time and Duration are recorded when the Compln By value is entered.

The procedures that correspond to Initialize, Start_Time, and End_Time are PopulateNewTable1Row, RecordTheStartTime, and RecordTheEndTime, respectively.  I make frequent use of the With statement to reduce the visual clutter (and likely slightly improve performance); it's documented in this Microsoft documentation.  But the significant difference is that the code in the latter procedures uses the table's properties rather than the worksheet's properties.

(I don't have knowledge of the conditions under which your procedures are called; I tried to invoke the Record...Time procedures only when the last row with a Task was changed in either the Task or Compln By columns.  I thought that the TableUpdate event might be triggered, but for most of my changes, it was not.)