Microsoft Office Forums - View Single Post

Catalin.B · 09-07-2014, 07:03 AM

Hi,
This post is not a help request, it's a method offered for those who need to have the defined table autoexpand functionalities on a protected sheet. By default, the autoexpand functionality will not work if the worksheet is protected.

You can test the file attached, it has the same code:

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)
OpenClipboard 0
If Range("AutoExpand") = "Disabled" Then CloseClipboard: Exit Sub
On Error Resume Next

If (Target.Row = Range("TestTable").Rows.Count + 2 And _
    Target.Column < Range("TestTable").Columns.Count + 1 And _
    Target.Cells.Count = 1 And _
    IIf(Target.Row > 1, Target.Cells.Offset(-1, 0).Locked = False, Target.Cells.Locked = False)) Or _
      (Target.Row < Range("TestTable").Rows.Count + 2 And _
       Target.Column = Range("TestTable").Columns.Count + 1) Then

   Unprotect
   CloseClipboard
Else
   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 If
On Error GoTo 0
End Sub

When a macro is triggered, this action will clear the clipboard by default, for this reason, i've used the OpenClipboard method to prevent from beeing emptied, you will be able to undo your last actions.
Enjoy!