CODEWITHSUNDEEP
excelvba
Mahmoud Tarief
Mahmoud Tarief

Reputation: 75

Run vba code on the selection of first listRow in excel

In a structured table when table is initially created the listRow is 0. Let us say in Sheet1 we have a listobject Table1 I need to run the following code: ThisWorkbook.Worksheets("Sheet1").ListObjects("Table1").ListRows.Add AlwaysInsert:=True. when Table1 has no rows and the first row is clicked or stepped in or any cell in the first row (The row after the header) is selected.

I need this because locked formula cell in protected sheet is not working unless there is at least 1 list row.

Problem Explained:

enter image description here

Upvotes: 1

Views: 705

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149325

Post Chat Discussion:

The problem was with the way you were creating the table and adding the formulas and protecting the sheet immediately after deleting the rows. Also you were trying to run the code on a protected sheet.

Try this

Creation of the table

I am taking the example of Cells A1:C1

A. In Cell A1, B1, C1, type "Header1, Header2, Header3"

B. Next Select A1:C1 and click on Insert | Table

C. Click on the Checkbox "My Table has headers"

D. Next in Cell C2, type the formula 

=IF([Header1]+[Header2]=0,"",[Header1]+[Header2])`

E. Protect the sheet keeping Col C locked and A/B unlocked.

And you are done.

Use this code to add new rows

Sub Sample()
    With ThisWorkbook.Worksheets("Sheet1")
        .Unprotect 
        .ListObjects("Table1").ListRows.Add AlwaysInsert:=True
        .Protect
    End With
End Sub

Remember when you reset the table by deleting the rows, ensure that you type something in cell A2 and B2 and then clearing the cells before you protect the sheet else you will loose your formulas.

Hope this helps

Upvotes: 1

Related Questions