I need to enter a 1 when a button is clicked

[dpaton05]

I have a table and as new rows get added, I need some vba code to enter a 1 into the column AB on the new row. Can someone help me with the syntax please? The table is located on a sheet called "home" and is called tblCosting. There are things below the table, so you can't use the xlup method of finding the last row.

Thanks,
Dave

 

[gallen]

Do you have the code you use to create the new line?

 

[dpaton05]

Sure, here it is:

Private Sub cmdAddRow_Click()

Dim tbl As ListObject

Set tbl = Worksheets("home").ListObjects("tblCosting")


    'ActiveSheet.Unprotect Password:="npssadmin"

    'add a row at the end of the table
    tbl.ListRows.Add
    'ActiveSheet.Protect Password:="npssadmin"
    


End Sub

 

[gallen]

Assuming your table starts in Column A. If not adjust as required

Private Sub cmdAddRow_Click()


Dim tbl As ListObject
Dim newRow As ListRow


Set tbl = Worksheets("home").ListObjects("tblCosting")


    'ActiveSheet.Unprotect Password:="npssadmin"


    'add a row at the end of the table
    
    Set newRow = tbl.ListRows.Add
    newRow.Range(28) = 1 'assuming the first column of your table is in Column A. Adjust as necessary
    
End Sub

 

[dpaton05]

Thank you, that is perfect

 

[gallen]

Welcome.

 

[dpaton05]

Found another problem with the last row.

When a row is entered, a custom figure may be put in place of the calculated figures. I have a text box below the table that I need to be able to copy to a field that will add it to the total.

The code I currently have is

Private Sub cmdEnterActivities_Click()
Worksheets("home").Unprotect Password:="costings"
    
    
    
    Sheets("home").Range("AC5") = txtActivities.Value
'Worksheets("home").Protect Password:="costings"

End Sub

This was when my table was just built for one row but my brain has stopped at the moment and I don't know how to get it to put the number in txtActivities into the column AC, but on the last row in the table, which will be the row that the user is working on. Can you help please?

 

[gallen]

A little confused but to get the last row of the table:

Dim lr As Long


  lr = Worksheets("home").ListObjects("tblCosting").Range.Cells(Rows.Count, 1).End(xlUp).Row

 

[dpaton05]

A little confused but to get the last row of the table:

Dim lr As Long


  lr = Worksheets("home").ListObjects("tblCosting").Range.Cells(Rows.Count, 1).End(xlUp).Row

I get the error message of "application defined or object defined error" and highlights the following row.

Here is my code:

Private Sub cmdEnterActivities_Click()
Worksheets("home").Unprotect Password:="costings"
Dim lr As Long
[COLOR=#ff0000]  lr = Worksheets("home").ListObjects("tblCosting").Range.Cells(Rows.Count, 1).End(xlUp).Row[/COLOR]
 
    Worksheets("home").Cells(lr, 29) = txtActivities.Value
'Worksheets("home").Protect Password:="costings"

End Sub

 

[gallen]

Strange. I Used the info you put in post #3 -

Set tbl = Worksheets("home").ListObjects("tblCosting")

as a reference.

To test it, I even set up a table named tblCosting and a worksheet called 'home'. Just tried again and it works fine.

Ensure you do indeed have a worksheet 'home' and a list object named 'tblCosting'