Excel First

Excel Solutions

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

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 Then Exit Sub ' 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 Sub

You 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 Sub

If you found situations in which the code is not working as expected, send me the details, only together we can make things better 🙂 (where did i hear that??)

Have fun 🙂

Cheers,

Catalin

27 Comments to Autoexpand Excel Tables on Protected Sheets

  1. Eduard says:

    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:

      Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
      Private Declare Function CloseClipboard Lib "user32" () As Long

      With this:

      #If VBA7 Then
      Private Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As LongPtr) As Long
      Private Declare PtrSafe Function CloseClipboard Lib "user32" () As Long
      #Else
      Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
      Private Declare Function CloseClipboard Lib "user32" () As Long
      #End If

      Or, you can download the sample workbook again, I updated the code for 64 bit systems.
      Cheers,
      Catalin

  2. Nicolas says:

    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,

     

  3. 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

  4. Julian De Silva says:

    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

  5. Christos says:

    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

  6. William says:

    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

  7. Dale says:

    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.

  8. Jackie says:

    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.

  9. Iris says:

    Awesome, thanks! Works like a charm on the fixed asset workbook I just created.

  10. Adalia VondemBerg says:

    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?

  11. Ben says:

    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?

  12. Ben says:

    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).

  13. Van Wooten says:

    Hi Catalin,

    Newbie here, Do you run this code in sheet activate event? The Overwriting columns code, where does it run?

    Van

  14. Van Wooten says:

    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

The Excel Learning Journey that I propose for you, is structured in such a way as to offer you the most practical way to learn Excel, free.

Start Your Free Excel Learning Journey!

Enter your email address to subscribe to this blog: