VBA Error Method of '_Default' if object 'range' failed when inserting into a table

Question

I am working on a UserForm that is linked to an Excel Workbook located on a network path. Within the notebook I have a table named Source. This table contains an ID and Source Name. Within the UserForm there is a button to add a new source to the table. My current VBA is as follows:

Private Sub bFinishAdd_Click()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rng As Range
    Dim lr As Long

    Set wb = Workbooks.Open("\\datapath\datasub1\datasub2\filename.xlsx")
    Set ws = wb.Worksheets("Source")
    Set rng = ws.Range("Source[Source]")

    If tbNewSourceName <> "" Then

        If Application.WorksheetFunction.CountIf(rng, tbNewSourceName) > 0 Then

            MsgBox "Source System already exists!"

            lbSourceSystems.Enabled = True
            bAddSource.Enabled = True
            frameAddSource.Enabled = False
            lblNewSourceName.Enabled = False
            bFinishAdd.Enabled = False
            bCancelAdd.Enabled = False
            tbNewSourceName = ""
            tbNewSourceName.Enabled = False
            tbNewSourceName.BorderStyle = fmBorderStyleNone

            Exit Sub

        Else

            lr = ws.Cells(Rows.Count, 1).End(xlUp).Row

            ws.Cells(lr + 1, 1) = lr - 1 + 1000
            ws.Cells(lr + 1, 2) = tbNewSourceName

        End If

    End If

End Sub

Adding a new source triggers the error "Method of '_Default' if object 'range' failed". Excel simply crashes and I cannot debug, but I know the error is caused by:

        ws.Cells(lr + 1, 1) = lr - 1 + 1000
        ws.Cells(lr + 1, 2) = tbNewSourceName

However, I don't understand why I'm receiving the error or how to fix it. Any ideas?

score 1 · Answer 1 · answered Nov 11 '17 at 23:04

1

Obviously using the default method is going wrong somewhere. You can't assign a textbox to a cell. So be explicit. Try:

ws.Cells(lr + 1, 1).Value = lr - 1 + 1000
ws.Cells(lr + 1, 2).Value = tbNewSourceName.Text

answered Nov 11 '17 at 23:04

itsLex

786
1
5
13

Thanks. This did not fix my error. However, I did figure out what was causing the issue. Within my UserForm there is a ListBox that is tied to the table I was adding to. It was fixed by removing the RowSource prior to adding to the table and adding the RowSource back after. – Sol_Kralj Nov 11 '17 at 23:20

score 1 · Answer 2 · answered Nov 11 '17 at 23:24

I discovered that the same table I was attempting to add to is listed as the RowSource for a ListBox on my UserForm. I updated the code to remove the RowSource prior to adding and adding it back after.

        lbSourceSystems.RowSource = ""

        lr = ws.Cells(Rows.Count, 1).End(xlUp).Row

        ws.Cells(lr + 1, 1).Value = lr - 1 + 1000
        ws.Cells(lr + 1, 2).Value = tbNewSourceName.Text

        lbSourceSystems.RowSource = ("'filename.xlsx'!SourceSystems")

VBA Error Method of '_Default' if object 'range' failed when inserting into a table

2 Answers2

Linked