[Dba-office] Excet Table with VBA

[RockySmolin at bchacc.com]

I've done a of of VBA behind Excel spreadsheets but none of it of my own
invention - I've always used the macro recorder, recorded my keystrokes
for whatever I wanted, then cribbed the resulting code out of the macro
editor.

But I always had a row count so when I wanted to add a line I just used
Cells(Row,Col) method.  I'm not sure how I would have recorded an 'add
new row' in a macro. 

r

-------- Original Message --------
Subject: Re: [Dba-office] Excet Table with VBA
From: Susan Harkins <ssharkins at gmail.com>
Date: Fri, December 18, 2015 7:37 am
To: Salakhetdinov Shamil <mcp2004 at mail.ru>,
dba-office at databaseadvisors.com

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
>
_______________________________________________
Dba-office mailing list
Dba-office at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-office