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

K

kbishop94

Active Member
Joined
Dec 5, 2016
Messages
458
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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')

VBA Code: 
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):

VBA Code: 
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:

VBA Code: 
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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
You need to resize the table instead of the named range.
Try replacing
VBA Code: 
Range("Table1").Resize(Range("Table1").Rows.Count + 1, Range("Table1").Columns.Count).Resize.Name = "Table1"
with this:
VBA Code: 
With Sheets("LookUpLists").ListObjects("Table1")
    .Resize .Range.Resize(.Range.Rows.Count + 1)
End With
 
Upvote 0
Solution
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.

VBA Code: 
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
 
Upvote 0
You're welcome, glad to help & thanks for the feedback.:)
 
Upvote 0
Alex Blakenburg said:
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.

VBA Code: 
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
Click to expand...
Excellent!!!! Thank you Alex Blakenburg
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
populating a textbox on a userform from selections that are made from a listbox that is one another userform (popup)...
Replies
4
Views
172
kbishop94
K
S
  • Solved
Resize Tables using Table Names from Col A and the new Row count from Corresponding Col B
2
Replies
13
Views
410
sspatriots
S
J
  • Question
Any advice on Append / Merge Power query using VBA
Replies
0
Views
783
juzzie
J
G
  • Question
VBA help - copy cells from specific table to specific table on another sheet.
Replies
0
Views
237
GardianAngelUK
G
H
  • Solved
Macro to sort block of data
2
Replies
15
Views
867
Joe4
Joe4

Forum statistics

Threads
1,214,591
Messages
6,120,425
Members
448,961
Latest member
nzskater

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top