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!
09-07-2014, 07:03 AM
Autoexpand Table on Protected Sheet
Similar Threads
Delete Rows in Protected Table with Form Fields
Windows Vista 
Office 2010 32bit
Linear Mode
