VBA to add data to a table

[Gem866]

I have a spreadsheet that uses a userform to collect name & address (and other) data. The results from the input of the userform are added to the last row of a particular worksheet (See code below)

I now want to convert that worksheet into a table so that when another routine looks up data from that table (or its used in a combobox) it will always display what's in the table and not dependant on searching a range in the worksheet. I have the following code which puts the info onto the last row of a worksheet. How does this code need to change if I now convert the worksheet data into a table and have new info from the userform added to the last row of a table. Assume the table name is 'CustomerList'

Private Sub CmdAddAcct_Click()
Dim aiRow As Long
Dim wsh As Worksheet
Set wsh = Worksheets("Accounts")

'find first empty row in database
aiRow = wsh.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).row + 1


With wsh
.Cells(aiRow, 1).Value = Me.TxtNewName.Value
.Cells(aiRow, 2).Value = Me.TxtNewAcct.Value
.Cells(aiRow, 3).Value = Me.TxtAttention.Value
.Cells(aiRow, 4).Value = Me.TxtAdd1.Value
.Cells(aiRow, 5).Value = Me.TxtAdd2.Value & " " & Me.TxtAdd3.Value

.Cells(aiRow, 6).Value = Me.TxtPCode.Value
.Cells(aiRow, 7).Value = Me.TxtSSComment.Value
.Cells(aiRow, 8).Value = Me.TxtCIComment.Value
.Cells(aiRow, 9).Value = Me.LstType.Value

End With

Look forward to some help. With thanks

 

[dmt32]

Hi,
If you convert your range to a Table & name it "Accounts" then try this update to your code & see if does what you want

Private Sub CmdAddAcct_Click()
    Dim wsh As Worksheet
    Dim tblAccounts As ListObject
    Dim NewRecord As ListRow
    
    Set wsh = ThisWorkbook.Worksheets("Accounts")
    
    'Define Table Object
        Set tblAccounts = wsh.ListObjects("Accounts")
    
    'Add New row to the table
    Set NewRecord = tblAccounts.ListRows.Add(AlwaysInsert:=True)
    
    With NewRecord
        .Range(1).Value = Me.TxtNewName.Value
        .Range(2).Value = Me.TxtNewAcct.Value
        .Range(3).Value = Me.TxtAttention.Value
        .Range(4).Value = Me.TxtAdd1.Value
        .Range(5).Value = Me.TxtAdd2.Value & " " & Me.TxtAdd3.Value
        .Range(6).Value = Me.TxtPCode.Value
        .Range(7).Value = Me.TxtSSComment.Value
        .Range(8).Value = Me.TxtCIComment.Value
        .Range(9).Value = Me.LstType.Value
    End With

End Sub

Dave

 

[MortezaRahmani]

Hey there,
thank you Dave, but I think Gem866 meant that the whole process should be done automatically (as well as the creation of the table) If I understood correctly!
And in completing your work, I suggest this :

Private Sub CmdAddAcct_Click()
    Dim wsh As Worksheet
    Dim tblAccounts As ListObject
    Dim baselc As String
    Dim NewRecord As ListRow
    
    Set wsh = ThisWorkbook.Worksheets("Accounts")
    
    'Checks if Exists and Creates CustomerList Table
     If wsh.ListObjects.Count < 1 Then
        baselc = Cells.SpecialCells(xlLastCell).Address
        wsh.ListObjects.Add(xlSrcRange, wsh.Range(baselc & ":" & Range(baselc).Offset(1, 9).Address), , xlYes).Name _
        = "CustomerList"
    End If
    
    'Define Table Object
        Set tblAccounts = wsh.ListObjects("CustomerList")
    
    'Add New row to the table
    Set NewRecord = tblAccounts.ListRows.Add(AlwaysInsert:=True)
    
    With NewRecord
        .Range(1).Value = Me.TxtNewName.Value
        .Range(2).Value = Me.TxtNewAcct.Value
        .Range(3).Value = Me.TxtAttention.Value
        .Range(4).Value = Me.TxtAdd1.Value
        .Range(5).Value = Me.TxtAdd2.Value & " " & Me.TxtAdd3.Value
        .Range(6).Value = Me.TxtPCode.Value
        .Range(7).Value = Me.TxtSSComment.Value
        .Range(8).Value = Me.TxtCIComment.Value
        .Range(9).Value = Me.LstType.Value
    End With
    
End Sub

 

[Gem866]

Hi,
If you convert your range to a Table & name it "Accounts" then try this update to your code & see if does what you want

Private Sub CmdAddAcct_Click()
    Dim wsh As Worksheet
    Dim tblAccounts As ListObject
    Dim NewRecord As ListRow
   
    Set wsh = ThisWorkbook.Worksheets("Accounts")
   
    'Define Table Object
        Set tblAccounts = wsh.ListObjects("Accounts")
   
    'Add New row to the table
    Set NewRecord = tblAccounts.ListRows.Add(AlwaysInsert:=True)
   
    With NewRecord
        .Range(1).Value = Me.TxtNewName.Value
        .Range(2).Value = Me.TxtNewAcct.Value
        .Range(3).Value = Me.TxtAttention.Value
        .Range(4).Value = Me.TxtAdd1.Value
        .Range(5).Value = Me.TxtAdd2.Value & " " & Me.TxtAdd3.Value
        .Range(6).Value = Me.TxtPCode.Value
        .Range(7).Value = Me.TxtSSComment.Value
        .Range(8).Value = Me.TxtCIComment.Value
        .Range(9).Value = Me.LstType.Value
    End With

End Sub

Dave

Thank you for this solution - It works perfectly

 

[dmt32]

Thank you for this solution - It works perfectly

welcome - feedback appreciated

Dave

 

[dmt32]

Hey there,
thank you Dave, but I think Gem866 meant that the whole process should be done automatically

I interpreted OP manually converting an existing Range to a Table hence my update to their published code - but all additions to a suggestion welcome as may help others looking for a similar solution.

Dave

 

[Gem866]

Hi,
If you convert your range to a Table & name it "Accounts" then try this update to your code & see if does what you want

Private Sub CmdAddAcct_Click()
    Dim wsh As Worksheet
    Dim tblAccounts As ListObject
    Dim NewRecord As ListRow
   
    Set wsh = ThisWorkbook.Worksheets("Accounts")
   
    'Define Table Object
        Set tblAccounts = wsh.ListObjects("Accounts")
   
    'Add New row to the table
    Set NewRecord = tblAccounts.ListRows.Add(AlwaysInsert:=True)
   
    With NewRecord
        .Range(1).Value = Me.TxtNewName.Value
        .Range(2).Value = Me.TxtNewAcct.Value
        .Range(3).Value = Me.TxtAttention.Value
        .Range(4).Value = Me.TxtAdd1.Value
        .Range(5).Value = Me.TxtAdd2.Value & " " & Me.TxtAdd3.Value
        .Range(6).Value = Me.TxtPCode.Value
        .Range(7).Value = Me.TxtSSComment.Value
        .Range(8).Value = Me.TxtCIComment.Value
        .Range(9).Value = Me.LstType.Value
    End With

End Sub

Dave

Could I request assistance with another associated matter please ?

Now that I have the table - Called Accounts - I am wanting to have a selection from a combobox assign a value from a lookup or an index and match to a textbox on the user form. As part of my userform initialisation I am selecting the data from the table for the combo box list as follows

Private Sub UserForm_Initialize()
With Application
.WindowState = xlMaximized
Zoom = Int(.Width / Me.Width * 100)
Width = .Width
Height = .Height
End With

With Sheets("Accounts")
Me.CmbChurch.List = .Range("Accounts[Acct Name]").Value
End With


End Sub

Once the selection has been made from the combobox I then want the account number to be displayed in a text box called TxtChurch. The following is how I am trying to do that but it doesnt work.

Private Sub CmbChurch_AfterUpdate()
'Lookup Values based on first Control
With Me

.TxtChurch = WorksheetFunction.Index(Range("Accounts[Acct #]"), WorksheetFunction.Match(CmbChurch, Range("Accounts[Acct Name]"), 0))
End With
End Sub

My Table 'Accounts' has the following headers
Acct Name Acct # Attention Address Suburb Pcode SSNotes CINotes Type

I want to lookup based on Acct Name (Which I think has become the selection in the Me.CMBChurch.value) and return data from Acct # into the TxtChurch textbox.

Hope you can help - Sorry I only know very little about VBA

Regards

 

[dmt32]

Hi,
It's a new question - post it to the forum where you will get a wider response.

Dave