willow1985
Well-known Member
- Joined
- Jul 24, 2019
- Messages
- 886
- Office Version
- 365
- Platform
- Windows
Hello,
I have been trying to solve a problem with a table that is on a protected sheet.
As I am sure many of you know Tables do not auto expand when the sheet is protected. I do however need the table to expand and have the sheet protected.
Any advise on a work around for this would be greatly appreciated.
Note: One of my ideas was to set off a macro when new data was entered in column C that would unlock the spreadsheet, expand the table and then re-lock the spreadsheet but I cannot seem to make this work/find the right code to expand the table without knowing the exact range.
The data starts at A3 and goes to the last row of data in column W, however there are some blanks periodically in various columns. The only column that does not have any blanks is column C.
The locked formulas are in columns L,M and N, there are other formulas that are not locked however in other columns.
There will also be situations when a macro will dump large blocks of data will be pasted in the last blank row in column A to column G so I don't know if using "new data in column C" as a trigger would work..
I hope someone can help as I feel stumped on this one.
My table name is "MF"
Thank you!
I have been trying to solve a problem with a table that is on a protected sheet.
As I am sure many of you know Tables do not auto expand when the sheet is protected. I do however need the table to expand and have the sheet protected.
Any advise on a work around for this would be greatly appreciated.
Note: One of my ideas was to set off a macro when new data was entered in column C that would unlock the spreadsheet, expand the table and then re-lock the spreadsheet but I cannot seem to make this work/find the right code to expand the table without knowing the exact range.
The data starts at A3 and goes to the last row of data in column W, however there are some blanks periodically in various columns. The only column that does not have any blanks is column C.
The locked formulas are in columns L,M and N, there are other formulas that are not locked however in other columns.
There will also be situations when a macro will dump large blocks of data will be pasted in the last blank row in column A to column G so I don't know if using "new data in column C" as a trigger would work..
I hope someone can help as I feel stumped on this one.
My table name is "MF"
Thank you!
VBA Code:
If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Range("C:C")) Is Nothing Then
If Target.Value <> "" Then
Worksheets("Master Forecast").Unprotect Password:="2150"
'Code to resize table or bring down data by 1 row??
Worksheets("Master Forecast").Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
:=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True, Password:="2150"
End If
End Sub