Adding Data form a User Form to a table

[skeeeter56]

Hope someone can help with this issue. I created Employee Attendance Tracker form the template provided by Microsoft. I am trying to make it easier to input the data with a user form. I found an example of what I need which works great, it all works. I have tried to copy the code and get it to work with the file I have created from the template. This is the code on the OK button which adds the data to a new row in the table

Private Sub bOkay_Click()

Dim tbl As ListObject
Dim ws As Worksheet
Dim lrow As Range
Dim lrow2 As Long


Set tbl = Sheets(1).ListObjects("[I][B][COLOR=#B22222]table1[/COLOR][/B][/I]")

    If tbl.ListRows.Count > 0 Then

        Set lrow = tbl.ListRows(tbl.ListRows.Count).Range
        For col = 1 To lrow.Columns.Count
            If Trim(CStr(lrow.Cells(1, col).Value)) <> "" Then
                tbl.ListRows.Add
                Exit For
            End If
        Next
    End If

    lrow2 = tbl.ListRows.Count

     tbl.DataBodyRange(lrow2, 1).Value = cboEmployee.Value

     tbl.DataBodyRange(lrow2, 2).Value = cboType.Value

     tbl.DataBodyRange(lrow2, 3).Value = sDate.Value

     tbl.DataBodyRange(lrow2, 4).Value = eDate.Value

 Unload Me

 EmployeeLeave.Show
End Sub

This all works as I want it to in the example I found. But I have tried to replace that table with the one in the template, that does add the data. But then the calendar part on another sheet does not work . So I then though if could just get this code to point to the table in the template that would work. I have tried both changing the table1 value to the new table name tbleLeave or rename the new table to table 1, but get an error

Runtime error 9
Subscript out of Range

Any ideas why and how I can get this to work

 

[Jerry Sullivan]

You didn't say which line was highlighted by the debugger when the error occurs, but from your description, I'll assume it was this line.

Set tbl = Sheets(1).ListObjects("table1")

If the Subscript out of Range error occurs there, it means Excel can't find a table with that name on the first Sheet of your workbook. It sounds like you've tried to make sure the name in the code matches the table name in the workbook.

Perhaps your table isn't on the first Sheet of your workbook? If so, you could reference the sheet by its name instead of sheet order. For example:

Set tbl = Sheets("Employee Leave Tracker").ListObjects("tbleLeave")

 

[skeeeter56]

Thanks that did the trick