problem with code after changing my named range to a named table range...

[kbishop94]

I have combobox on a userform that allows the user to select from a range of customer names. The combobox previously referred to a named range (NOT a table.) (The code worked fine when it was a non-table range btw.)

If the customer name that the user is looking for isnt present within the list of dropdown choices, then the user can select a button which then opens a second userform (a popup form) where the user can add a new name that dynamically adds it to the named range.

Like I said, this currently does work (as a named range), but its less than ideal so I recently changed that named range to a table. This works much better, but, I am having a problem with a line of code that now gives me an error. The line of code is within thepreviously mentioned 'popup form' that the user accesses when the name they are looking for isnt part of the combobox chocies.

Here is the line of code that is causing problems: (it gives 'run-time error 450 invalid property assignment')

Range("Table1").Resize(Range("Table1").Rows.Count + 1, Range("Table1").Columns.Count).Resize.Name = "Table1"

Here is the complete code before I changed it to a table (this works btw):

Private Sub cmdAddNewCustomer_Click()
Dim ws As Worksheet
Set ws = Worksheets("LookUpLists")
Dim llRow As Long
    llRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
Dim j As Long
Dim c As Long
    With Sheets("LookUpLists")
        j = .Range("Customers").Row
        c = .Range("Customers").Column
'
    Do While Not IsEmpty(.Cells(j, c))
        j = j + 1
    Loop
'
    .Rows(j).EntireRow.Insert
    .Rows(j).EntireRow.FillDown
'
Me.txtAddNewCustomerName.value = UCase(Me.txtAddNewCustomerName.value)
.Cells(j, c + 0).value = Me.txtAddNewCustomerName.value
'
Range("Customers").Resize(Range("Customers").Rows.Count + 1, Range("Customers").Columns.Count).Resize.Name = "Customers"
'
ActiveWorkbook.Worksheets("LookupLists").Activate
        With ActiveWorkbook.Worksheets("LookupLists").Sort
            .SortFields.Clear
            .SortFields.Add Key:=Range("Customers").Columns, _
                SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

            .SetRange Range("Customers")
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    MsgBox "The new customer you entered: " & UCase(Me.txtAddNewCustomerName) & vbNewLine & "has been added to the Customer List database." & vbNewLine & " " & vbNewLine & "Please reopen the 'ENTER NEW INCIDENT' form to re-enter your incident."
'
    Unload Me
    frmIncidentEntry.cboCustomer.value = Me.txtAddNewCustomerName.value
'
    End With
End Sub

Now here is the complete code after I changed it to a table (the line of the 'causing me problems' code I posted above is from this userform module... the Range("Table1") code located about in the middle:

Private Sub cmdNewAddCustomer_Click()
Dim ws As Worksheet
Set ws = Worksheets("LookUpLists")
Dim llRow As Long
    llRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
Dim j As Long
Dim c As Long
    With Sheets("LookUpLists")
        j = .Range("Table1").Row
        c = .Range("Table1").Column
'
    Do While Not IsEmpty(.Cells(j, c))
        j = j + 1
    Loop
'
    .Rows(j).EntireRow.Insert
    .Rows(j).EntireRow.FillDown
'
Me.txtAddNewCustomerName.value = UCase(Me.txtAddNewCustomerName.value)
.Cells(j, c + 0).value = Me.txtAddNewCustomerName.value
'
Range("Table1").Resize(Range("Table1").Rows.Count + 1, Range("Table1").Columns.Count).Resize.Name = "Table1"
'
ActiveWorkbook.Worksheets("LookupLists").Activate
        With ActiveWorkbook.Worksheets("LookupLists").Sort
            .SortFields.Clear
            .SortFields.Add Key:=Range("Table1").Columns, _
                SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SetRange Range("Table1")
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    MsgBox "The new customer you entered: " & UCase(Me.txtAddNewCustomerName) & vbNewLine & "has been added to the Customer List database."
'
    Unload Me
    frmIncidentEntry.cboCustomer.value = Me.txtAddNewCustomerName.value
'
End With
End Sub

 

[Akuini]

You need to resize the table instead of the named range.
Try replacing

Range("Table1").Resize(Range("Table1").Rows.Count + 1, Range("Table1").Columns.Count).Resize.Name = "Table1"

with this:

With Sheets("LookUpLists").ListObjects("Table1")
    .Resize .Range.Resize(.Range.Rows.Count + 1)
End With

 

[Alex Blakenburg]

This is a bit long winded but this might work for you.
It probably doesn't apply in your case but it caters for there being no data rows already in the table and also for there being empty rows at the bottom of the table.

Note: In the sort section I have "OrderDate" as my sort field. Please change that to the heading of the field you want to sort the table on.

Private Sub cmdNewAddCustomer_Click()
    Dim ws As Worksheet
    Dim lstObj As ListObject
    Dim lastRow As Long
    Dim lastRowOfTbl As Long
    Dim loRow As ListRow
    Dim strCustName As String
   
    Set ws = Worksheets("LookUpLists")
    Set lstObj = ws.ListObjects("Table1")
   
    strCustName = UCase(Me.txtAddNewCustomerName.Value)
           
    lastRow = lstObj.Range.Columns(1).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   
    With lstObj
        If .ShowAutoFilter Then .AutoFilter.ShowAllData

        ' Get Last Used Row of table
        lastRow = lstObj.Range.Columns(1).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
       
        ' Get Last Row of Table
        lastRowOfTbl = .HeaderRowRange.Cells(1, 1).Offset(.ListRows.Count).Row
       
        ' Check for no data rows in table or last use row = last row in table
        If lastRow = .HeaderRowRange.Cells(1, 1).Row Or lastRow = lastRowOfTbl Then
            ' If no unused rows in table then add a row
            Set loRow = lstObj.ListRows.Add()
            loRow.Range.Resize(, 1) = strCustName
        Else
            ' Use the first unused row in the table
            ws.Cells(lastRow + 1, .ListColumns(1).Range.Column).Resize(, 1) = strCustName
        End If
    End With
'
    With ws.Sort
        .SortFields.Clear
        ' XXX Change the Table Column Name for the Sort in the next line
        .SortFields.Add Key:=Range("Table1[OrderDate]").Columns, _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range("Table1")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    MsgBox "The new customer you entered: " & strCustName & vbNewLine & "has been added to the Customer List database."
'
    Unload Me
    frmIncidentEntry.cboCustomer.Value = Me.txtAddNewCustomerName.Value
'
End Sub

 

[kbishop94]

Awesome! Thank you, Akuini

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.

 

[kbishop94]

This is a bit long winded but this might work for you.
It probably doesn't apply in your case but it caters for there being no data rows already in the table and also for there being empty rows at the bottom of the table.

Note: In the sort section I have "OrderDate" as my sort field. Please change that to the heading of the field you want to sort the table on.

Private Sub cmdNewAddCustomer_Click()
    Dim ws As Worksheet
    Dim lstObj As ListObject
    Dim lastRow As Long
    Dim lastRowOfTbl As Long
    Dim loRow As ListRow
    Dim strCustName As String
  
    Set ws = Worksheets("LookUpLists")
    Set lstObj = ws.ListObjects("Table1")
  
    strCustName = UCase(Me.txtAddNewCustomerName.Value)
          
    lastRow = lstObj.Range.Columns(1).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  
    With lstObj
        If .ShowAutoFilter Then .AutoFilter.ShowAllData

        ' Get Last Used Row of table
        lastRow = lstObj.Range.Columns(1).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
      
        ' Get Last Row of Table
        lastRowOfTbl = .HeaderRowRange.Cells(1, 1).Offset(.ListRows.Count).Row
      
        ' Check for no data rows in table or last use row = last row in table
        If lastRow = .HeaderRowRange.Cells(1, 1).Row Or lastRow = lastRowOfTbl Then
            ' If no unused rows in table then add a row
            Set loRow = lstObj.ListRows.Add()
            loRow.Range.Resize(, 1) = strCustName
        Else
            ' Use the first unused row in the table
            ws.Cells(lastRow + 1, .ListColumns(1).Range.Column).Resize(, 1) = strCustName
        End If
    End With
'
    With ws.Sort
        .SortFields.Clear
        ' XXX Change the Table Column Name for the Sort in the next line
        .SortFields.Add Key:=Range("Table1[OrderDate]").Columns, _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range("Table1")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    MsgBox "The new customer you entered: " & strCustName & vbNewLine & "has been added to the Customer List database."
'
    Unload Me
    frmIncidentEntry.cboCustomer.Value = Me.txtAddNewCustomerName.Value
'
End Sub

Excellent!!!! Thank you Alex Blakenburg

 

[Alex Blakenburg]

You're welcome.