Mar 14 2023 05:51 AM
I have a table with one protected column and few columns where I can add data.
Since I don't want to delete any formula or rename any header by mistake.
I wanna protect the sheet, but then the table doesn't expand on its own, with the last row entry
I'm attaching a sample file. Can someone help me with the macro?
Assuming that I can always expland the columns ( B, C, D) where I need to enter data
Mar 14 2023 06:13 AM
Adding 500 rows will be slow, but this should do it:
Sub Add_500_Rows()
Dim i As Long
Application.ScreenUpdating = False
ActiveSheet.Unprotect
With ActiveSheet.ListObjects(1)
For i = 1 To 500
.ListRows.Add
Next i
End With
ActiveSheet.Protect
Application.ScreenUpdating = True
End Sub
Mar 14 2023 12:23 PM
SolutionMy VBA programming is not really up to scratch, but I attempted to use an event handler to add rows one at a time.
Sheet1
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim LO As ListObject
Dim nextRow As Range
Dim n As Variant
Set LO = ListObjects(1)
n = LO.DataBodyRange.Rows.Count
Set nextRow = LO.ListRows(n).Range.Offset(1)
If Not Intersect(Target, nextRow) Is Nothing Then Add_a_Row
End Sub
Module1
Sub Add_a_Row()
Application.ScreenUpdating = False
With ActiveSheet
.Unprotect
.ListObjects(1).ListRows.Add
.Protect
End With
Application.ScreenUpdating = True
End Sub
I seems to work but is far from robust.
Mar 14 2023 12:23 PM
SolutionMy VBA programming is not really up to scratch, but I attempted to use an event handler to add rows one at a time.
Sheet1
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim LO As ListObject
Dim nextRow As Range
Dim n As Variant
Set LO = ListObjects(1)
n = LO.DataBodyRange.Rows.Count
Set nextRow = LO.ListRows(n).Range.Offset(1)
If Not Intersect(Target, nextRow) Is Nothing Then Add_a_Row
End Sub
Module1
Sub Add_a_Row()
Application.ScreenUpdating = False
With ActiveSheet
.Unprotect
.ListObjects(1).ListRows.Add
.Protect
End With
Application.ScreenUpdating = True
End Sub
I seems to work but is far from robust.