Autoexpand Excel Tables on Protected Sheets
Autoexpand Excel Tables on Protected Sheets
Scenario:
An important feature of Excel Defined Tables, the autoexpanding functionality, is lost when the worksheet is protected. On a protected worksheet, when you type anything under the last row, the table will not automatically expand to include the new row, as you might expect. The decision to protect the sheet is not easy when you need both protection and autoexpansion capabilities for an Excel Table.
Most likely, Microsoft engineers are well aware of this limitation, but until they come up with a solution to this problem, you can try my solution. The solution involves a little Visual Basic programming, but fortunately for you, I have taken care of the programming part.
Using the Worksheet_SelectionChange event,
we can detect what cell is selected by a user, and then we can decide if we need to unprotect (or not) the worksheet.
The best thing is that we can protect from editing important columns from the table, if there are such columns in your table, and still take full advantage of the table autocomplete features.
In this sample workbook, I setup 2 columns where a user must “stay away” from, they are the blue columns in this image:
Protected Columns in Excel Table
Important! To protect those columns, I simply set manually the Locked property of the cell to Locked (from the right click menu, Format cells, Protection tab, check the Locked checkbox).
The code will check the first cell from above the selected cell, it will reprotect the table if that cell is protected:
Target.Cells.Offset(Off, 0).Locked = False
Download the sample workbook: Autoexpand table on protected sheet.xlsm
Here is the VBA code:
Option Explicit
Private Declare Function OpenClipboard Lib "User32" (ByVal hwnd As Long) As Long
Private Declare Function CloseClipboard Lib "User32" () As Long
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Or Target.Cells(1).Locked = True Then GoTo ExitCode ' unprotect only when a single cell is selected
If Sheets("Switch").Range("AutoExpand") Like "Disabled" Then Exit Sub
Dim Tbl As ListObject, Off As Integer, ExitCode As Label
Dim TblFirstRow As Long, TblFirstColumn As Integer
Dim FirstRowAllowed As Long
On Error GoTo ExitCode
Off = 0: If Target.Row > 1 Then Off = -1
Set Tbl = ActiveSheet.ListObjects(1)
TblFirstRow = Tbl.HeaderRowRange.Row
TblFirstColumn = Tbl.HeaderRowRange.Cells(1, 1).Column
OpenClipboard 0 ' when a macro runs, usually the clipboard is emptied; opening the clipboard will preserve whatever you have in there;
FirstRowAllowed = TblFirstRow ' the table will be unprotected if the user selects a cell from this row down
If Target.Row >= FirstRowAllowed And Target.Row <= Tbl.ListRows.Count + TblFirstRow + 1 And _
Target.Column <= Tbl.ListColumns.Count + TblFirstColumn And _
Target.Cells.Offset(Off, 0).Locked = False Then
Unprotect
CloseClipboard
Else
GoTo ExitCode
End If
Exit Sub
ExitCode:
Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, UserInterfaceOnly:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingRows:=True, AllowSorting:=True, _
AllowFiltering:=True, AllowUsingPivotTables:=True
CloseClipboard
End SubYou will notice that I am opening and closing the Clipboard in this code.
The reason for this, is that when a code is triggered, the clipboard is cleared; if the Clipboard is open, Excel will fail to clear the clipboard, and whatever we copied before selecting a cell is still on the clipboard.
You can disable the code, if you need to make structural changes to the table, by setting the Enable or Disable option in the “Switch” worksheet.
The code will work in Excel 2007, 2010, and 2013; not tested in Excel 2003, but it should work there as well.
There are many possible situations that needs to be taken into consideration, like what happens if a user pasts multi column data into our table, overwriting protected columns?
With the existing code, it’s possible, but we can prevent that with another simple code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Tbl As ListObject
Set Tbl = ActiveSheet.ListObjects(1)
If Not Intersect(Target, Tbl.Range) Is Nothing Then
With Application
.EnableEvents = False
If Target.Columns.Count > 1 Then .Undo
.EnableEvents = True
End With
End If
End SubIf you found situations in which the code is not working as expected, send me the details, only together we can make things better 🙂
Have fun 🙂
Cheers,
Catalin
This is exactly what I have been looking for all weekend and finally found it.
But unfortunately I’m not able to try it as it starts the VBA editor and prompts an error message when opening the file.
The error message is as followed:
“Compile error:
The code in this Project must be updated for use on 64-bit systems.
Please review and update Declare statements and then mark them with the PtrSafe attribute.”
Hi Eduard,
You have to replace the function declarations:
Replace these 2 lines:
With this:
Or, you can download the sample workbook again, I updated the code for 64 bit systems.
Cheers,
Catalin
Hello,
it seems to be very effective for adding a new row, but with the macro running, I seems impossible to delete any row in the table.
Did you face this issue?
Best regards,
Hi Nicolas,
Check the Switch sheet, you can find there a dropdown that is used to disable the code, in order to make manual changes, like deleting rows.
Cheers,
Catalin
Hi Caitlin.
Unfortunately when sharing a spreadsheet using OneDrive, which allows multiple users to edit at the same time, by using the Edit in Browser option both VBA and macros are disabled. With this usage in mind is there any way to both protect formulas and allow the autoexpand functionality.
I must be honest that Edit in Browser really restricts the functionality of Excel.
Cheers
Julian
I’m afraid that there are no other options on OneDrive, that limitation can be bypassed only with VBA, but as you already know, the code will not work in browser.
Hopefully they will add this functionality on protected sheets.
Cheers,
Catalin
Hi Catalin
The sample file that you have shared has a option to enable disable protection. My requirement is to keep the worksheet protected by default & should be able to enter data into the table and for it to get auto expanded. I dont want to have any buttons to enter new rows.
All i want is to go on entering data into the protected sheet by auto expanding the table.
I tried editing your code, but i messed it up big time. Appreciate your help on this.
Cheers
Julian
Hi Julian,
Protection is enabled, if you want to remove the option to enable-disable the code, simply remove this line from code:
If Sheets(“Switch”).Range(“AutoExpand”) Like “Disabled” Then Exit Sub
Cheers,
Catalin
Hurrayyyy it works Catalin. Thanks a million for your prompt response.
Glad to hear you managed to make it work as you wish 🙂
Cheers
Catalin
Hi, Catalin
First of all thank for the time you consumed for us to share that option. I am new to VBA and I have one problem. When in another “sheet2” i use the “sumifs” formula and i use columns (ie A11:A) either protected or unprotected, from the protected “sheet1”, autoexpand does not work.
Hi Christos,
Sorry for the late reply, hope you did solved the problem by now.
If it’s still not working, can you please share a sample file? The description is not very clear, maybe a file will help understanding your problem.
Regards,
Catalin
Auto Expand Tables on protected sheet. I would like to attach my file to see if it is possible to alter the code to fit this workbook.
Thank you so much in advance
Greetings Catalin,
I ran across your posts while searching for a solution to my own excel issue.
I have several independent excel worksheets that are linked to a master sheet. The individual sheets’ cells are formatted to autowrap and autofill when the text in them runs long (keeping columns at a set width).
The master sheet is 99% protected, with the exception of a few vlookup cells. When data from the independent sheets automatically populates the master, upon opening, the cells that have multiple lines do not appear in the master; I am having difficulty telling the master sheet’s cells to autofill row height to accommodate multiple lines of text, and then to shrink back down when the multiple lines change back to one line of text.
Any suggestions would be a tremendous help.
Thank you,
Dale
Hi Dale,
Autofit row height does not work when there are formulas in those cells. The only way I know is to calculate in VBA the necessary height for those rows, based on that cell text length and font size.
Hi Catalin,
Trying to break down the code for a more in-depth understanding. Got confused with this line of code:
Off = 0: If Target.Row > 1 Then Off = -1
What does Off refer to?
I noticed that it was also used in the Offset method afterwards.
Target.Cells.Offset(Off, 0).Locked = False Then
Unprotect
I look forward to your reply. Thanks!
Hi Jackie,
Off is just a safety measure: when user selects the very first row, the code will no longer be able to check the previous row, in the line you mentioned: Target.Cells.Offset(Off, 0).Locked = False Then
The normal value of the Off parameter should be -1, the code needs to check if the cell above is editable to unprotect the sheet.
Awesome, thanks! Works like a charm on the fixed asset workbook I just created.
Catalin,
I’ve got one cell that with the code on, the data validation is not copied down onto the new row (all other data validations copy down just fine), but it works with the code off.
Can you point me in the right direction of where to look and what to look for to fix it?
Hi Adalia,
Can you please provide the file? Without analyzing the file, there is not much to tell.
Cheers,
Catalin
Hi Catalin,
I’m using C# instead of VBA. From what I’ve read, it looks like you are using the system clipboard. I was wondering if you can use the office clipboard instead.
I’m trying to keep the “marching ants” around the copied selection and keep the Paste Special option available.
Thanks!
Ben
PS Here is my C# code incase it’s helpful.
[System.Runtime.InteropServices.DllImport(“User32”)]
private static extern long OpenClipboard(long hwnd);
[System.Runtime.InteropServices.DllImport(“User32”)]
private static extern long CloseClipboard();
Hi Ben,
The only purpose of opening the clipboard is to preserve the content for copy-paste made with the usual Ctrl+C – Ctrl+V, I am not using the content in any way. I never tested if the office clipboard gets cleared when a procedure runs, is it?
To be honest, I’m not sure. Before the code runs to to protect/unprotect the sheet, I have multiple paste options (paste, paste values, paste formulas, etc.). After the sheet is protected/unprotected, I only have two paste options (paste and match destination formatting).
Well, after a code runs, usually the clipboard is cleared completely, just having only the first paste option is a plus.
Hi Catalin,
Newbie here, Do you run this code in sheet activate event? The Overwriting columns code, where does it run?
Van
Hi Van,
Code is using the SelectionChange and Change events, not sheet activate.
You will find the codes in that sheet vba module.
Thanks
Hi,
In my workbook i have 52 sheets (week 1 to week 52). Every sheet has 7 tables under each other (monday to sunday). It is used for a planning system for cars. The tables cannot be bigger than 40 rows, because this will never happen. This is why the tables are under each other.
When i use your code it only works on the second table (tuesday). The first table as also the third to seventh table does not expand. Do you know why it is only working for table 2?
Hi Xaba,
Works only for first table (by index, not position in sheet):
Set Tbl = ActiveSheet.ListObjects(1)
The structure you mentioned is not right, it certainly needs to be improved, you’ll need more and more complex solutions to get even simple reports from that structure.
Hi Catalin, Thank you for the fast reply.
Does this mean that it is not possible to set up the code so that it expands every table?
It is possible, of course.
Paste this function in a module in your project:
Function GetTable(Cell As Range) As ListObject
Dim tbl As ListObject
On Error Resume Next 'try to see if the cell is within a table
Set tbl = Cell.ListObject
If tbl Is Nothing Then
'try to see if the table is above 1 row:
Set tbl = Cell.Offset(-1, 0).ListObject
End If
If tbl Is Nothing Then
'if still nothing, it might be 1 cell to the left:
Set tbl = Cell.Offset(0, -1).ListObject
'should not be in the header row
If Not tbl Is Nothing Then
If Cell.Row = tbl.HeaderRowRange.Row Then Set tbl = Nothing
End If
End If
Set GetTable = tbl
End Function
Then, in the original code, change this line:
Set Tbl = ActiveSheet.ListObjects(1)
with:
Set Tbl = GetTable(Target): if tbl is nothing then exit sub
It should work now.
Thank you for your suggestion Catalin. Unfortunately it does not change anything. It still only works on one table.
I’ve pasted the function in a module and have adjusted the line in the original code.
Is there anything else i need to adjust in the renewed code? I am trying to attach the workbook, but that is not possible.
you replaced only in the Change procedure. Needs to be replaced in both.
The second reason for not working is that all cells in your table are locked.
you have to follow the instructions from the article and decide which columns are allowed to edit.
I indeed forgot to change it in both procedures. It now works, but only when I’m in the row directly under the table. When the sheet is unprotected (by clicking in the cell that is unprotected under the table) and I click just somewhere random in the sheet, for example the column next to the table, it does not reprotect the sheet again.
With the previous code that only works for 1 table, it reprotects the sheet when I click somewhere random. Can the code be adjusted for the new case so that it reprotects the sheet when I click somewhere random in the sheet?
Besides – when I change the procedures first declarations, so that it works in ThisWorkbook, with:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
it gives the following error:
Compile error: Named argument not found (it highlights the “drawingobjects:=”)
Did I do something wrong?
I am aware that this structure is not optimal. The reason for it is to give the user the possibility to have a table for every day of the year. To prevent to have to many sheets they are placed in sheets per week.
Instead of Protect, use Sh.Protect
Same for Unprotect, use Sh.Unprotect
Did you changed the cell locked status for the columns you want to allow edit?
By changing the protect and unprotect to Sh.protect and Sh.unprotect it now works in the ThisWorkbook event 🙂
For the columns i want to allow editing i changed the cell locked status to unlocked. Unfortunately when the sheet is unprotected, by clicking in an unprotected cell directly under the table, it does not reprotect it when i select a random cell in the sheet. Do you know why?
This line:
Set Tbl = GetTable(Target): if tbl is nothing then exit sub
Should be:
Set Tbl = GetTable(Target): if tbl is nothing then GoTo ExitCode
Dear Catalin,
It seems that the workbook does exaclty what I want know.
Thank you for the help and suggestions!
Kind regards,
Xaba
You’re welcome, glad to hear it works as expected.
Amazing! Thank you!!!
You’re welcome 🙂
Hi Catalin.
Thanks for the great code you provided, but I have a comment just in case you can address it somehow.
If the user selects more than one cell in the protected column, the code will not run and he stills be able to view and edit/delete the formulas.
Hi Mahmoud,
Indeed, this scenario is not covered, thanks for pointing it out.
You can change the first line of code from:
If Target.Cells.Count > 1 Then Exit Sub
to:
If Target.Cells.Count > 1 Or Target.Cells(1).Locked = True Then GoTo ExitCode
I fact I got the same result when I removed the line totally.
Is this will make a negative impact on the code?
Removing the line completely may cause other unwanted behavior.
If all works in your usage scenarios, no need to change anything, but if something is not working as expected, just add the line provided.
A simple way around the original problem is to not use Protect Sheet, but rather just validate the forbidden cells with Text Length Less than 1. This will show green validation error flags on each cell, but you can ignore those (there is a setting in Options that hides them, but then you’d not be notified about errors in other cells).
Simple indeed, but only if you are comfortable with the fact that any user can easily disable that validation, so it’s not really a protection.
Thanks for the tip
Oh I absolutely agree Catalin, and I’m not denigrating your article at all – indeed, you even account for users pasting data.
However, in many situations to make a workable sheet for simple data entry by people with a brain (all my colleagues for example), it is really only necessary to prevent accidental data entry and perhaps hide the protected columns if showing them is not necessary. It also works in Julian Richardson’s “Edit in Browser” mode.
It has worked for the five years I’ve been in my current job!
Well, a solution is best to be fool proof, even if it will be used by people with brain 🙂
Data entry is a complex subject, most of the times the data storage needs to be separated from data entry (using forms for collecting data will be better).
Editing the data directly in the archive is not the best way to do it, typing data in an area that is also used as a visual report is a bad idea, it’s like eating in the bathroom… 🙂
What I do and recommend is collecting data using forms (even online forms), store collected data in an archive sheet, then use the archive to build reports.
Cheers,
Catalin
Hi Catalin,
I’m far from an expert, but I’m trying your method. Unfortunately your example file ‘Autoexpand table on protected sheet.xlsm’ doesn’t work for me: If I protect the sheet and then write something in a cell in the first row under the table, the table does not autoexpands…
Hi Ivan,
I assume macros are not disabled?
Hi Catalin,
I tried your template and it works like a charm but the Protection of the sheet is removed after adding the rows automatically. Is there any way to re-protect the sheet after adding the rows? Thank you.
Hi Ariel,
If you try to select a locked cell, the protection should be applied automatically.
Hi Catalin,
I tried to make it work on my workbook but couldn’t succeed. It is only working on the template sheet. I thought the existing macros might be causing some interference so I created a new workbook and couldn’t work with it either.
2ndly I need it to work with 3 tables on the sheet.
I tried for hours and read all the comments but couldn’t work it out.
Looking forward for your reply.
Hi Saud,
Depends on how the tables are placed on sheet:
Code should be adjusted if they are side by side, or one under another, cannot be the same code.
Sent an email for clarifications.
Hi Catalin,
I didn’t receive an email from you yet except the notification of your reply. Btw, my tables are side by side.
Checked spam as well?
The solution is simple:
Instead of assigning the first table to the tbl variable, you have to assign the current cell ListObject.
In code, instead of:
Set Tbl = ActiveSheet.ListObjects(1) ‘first table in sheet
Use:
Set Tbl = Target.ListObject ‘current cell list object
And of course, you have to check the result and exit sub if there is no table:
If tbl is Nothing Then Exit sub
You have to replace this line in both procedures: Worksheet_Change event and Worksheet_SelectionChange event.
Like Catalin said, if they are arranged down the page, simple VBA tied to a button to unprotected, addrow, protect. With Addrow you can fill some data if you want like maybe a date.