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:

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 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 🙂

Have fun 🙂

Cheers,

Catalin