VBA to dynamically resize table when data is added on protected sheet

Question

please can you help with VBA code to automatically resize my table once data is added to a protected sheet? When the sheet is unlocked and I add data the table automatically resizes but when it's locked it does't resize to include any data added.

I currently have this and it inserts 1 row only, I need it to resize the table to fit the number of rows where data is added (it could be 10 rows or 100):

Sub ExtendTable()
    ActiveSheet.Unprotect Password:="XXXX"
    ActiveSheet.ListObjects("PortfolioTracker").ListRows.Add.Range.Locked = False
    ActiveSheet.Protect Password:="XXXX"
End Sub

Zohir Emon · Accepted Answer · 2022-10-26 06:30:55Z

0

How you will add data to the protected sheet? If you can add data to the protected sheet, then you can create a dynamic name range with =offset() formula. Else if you are strick with VBA then you have to write the code, then you need to write the code under SheetChange event.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

' Code will be here

End Sub

answered Oct 26, 2022 at 6:30

Zohir Emon

1041 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

allthem Over a year ago

Only certain cells are protected. It's basically a policy renewal list - we monitor the performance of the renewals - if they renew or cancel etc. The lists are generated every 3 months for the next set of renewals up and coming so they are able to add data.

allthem Over a year ago

I've got this far, it extends the table rows to whatever data is added but I can't get it to extend columns in case they decide they want to track additional information: ``` Sub ExtendTableRows() ActiveSheet.Unprotect Password:="X" Dim tbl As ListObject Set tbl = ActiveSheet.ListObjects("PortfolioTracker") With tbl.Range tbl.Resize .Resize(.CurrentRegion.Rows.Count) End With ActiveSheet.Protect Password:="X", AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True, DrawingObjects:=True, Scenarios:=False, AllowDeletingRows:=True End Sub ```

Collectives™ on Stack Overflow

VBA to dynamically resize table when data is added on protected sheet

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related