[Dba-office] Excet Table with VBA

[Susan Harkins]

Thanks Shamil -- it's similar to what I ended up with last night. Let's
compare! I think the only real difference is selecting the newly inserted
row. I knew going in that Tables are internally, a list object, but I had
anticipated more Table properties and methods, but nope... :) There really
should be an Add method that lets you specify (optional) new values for the
new rows! I expected that, but didn't find it.

I find Excel's object model confusing and I struggle to find the right data
types and objects in VBA.

Thank you for helping! -- Susan H.

Private Sub CommandButton1_Click()
    'Enter value into ListPerson column in Table1.

    Dim str As String
    Dim dbl As Double
    Dim rng As Range
    Dim oNewRow As ListRow

    str = TextBox1.Value

    Set rng =
ThisWorkbook.Worksheets("DataValidation").Range("Table1[ListPeople]")

    'Handle Match error when match not found.

    If Not IsError(Application.Match(str, rng, 0)) Then
        dbl = Application.Match(str, rng, 0)
    Else
        dbl = 0
    End If

    'If match is found, display duplicate error.
    'If match not found, add new record to Table1.
    If dbl <> 0 Then
        MsgBox "You can't enter duplicate values.", vbOKOnly, "Error"
        Exit Sub
    Else
       'Select Table1.
        rng.Select
        Set oNewRow = Selection.ListObject.ListRows.Add(AlwaysInsert:=True)
        oNewRow.Range.Cells().Value = str
        Set oNewRow = Nothing
    End If

End Sub



On Fri, Dec 18, 2015 at 8:38 AM, Salakhetdinov Shamil <mcp2004 at mail.ru>
wrote:

>  Hi Susan --
>
> Here it's:
>
> Option Explicit
> Public Sub testAddXlTableRow()
> '
> https://msdn.microsoft.com/en-us/library/office/ff196248(v=office.15).aspx
> Dim lst As ListObject
> Set lst = ThisWorkbook.Worksheets("DataValidation").ListObjects("Table1")
> ' add an empty row and seed its cells' values with
> ' this added row index
> Dim listRow As listRow
> Dim cellIndex As Integer
> Set listRow = lst.ListRows.Add(AlwaysInsert:=True)
> For cellIndex = 1 To listRow.Range.Cells.Count
> listRow.Range.Cells(cellIndex) = listRow.Index
> Next cellIndex
> End Sub
> -- Shamil
>
> >Thursday, December 17, 2015 5:48 PM -05:00 from "Susan Harkins" <
> ssharkins at gmail.com>:
> >
> >I can add a new row to an Excel Table using the following line of code:
> >
> >
> >
> >
>
> >ThisWorkbook.Worksheets("DataValidation").ListObjects("Table1").ListRows.Add
> >AlwaysInsert:=True
> >
> >
> >
> >But for the life of me I can't figure out how to add a value to the new
> row!
> >I've read all day and I can find all kind of articles on the above - but
> >nothing on how to add a value to that new row.
> >
> >
> >
> >Susan H.
> >
> >_______________________________________________
> >Dba-office mailing list
> >Dba-office at databaseadvisors.com
> >http://databaseadvisors.com/mailman/listinfo/dba-office
>
> _______________________________________________
> Dba-office mailing list
> Dba-office at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-office
>