#1
|
|||
|
|||
Autoexpand Table on Protected Sheet
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 Enjoy! |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Delete Rows in Protected Table with Form Fields | Elan05 | Word VBA | 23 | 09-11-2014 12:47 PM |
Add rows in protected table with Form Fields | Apriljade | Word | 2 | 02-26-2014 06:42 AM |
Using macro to add variable number of rows to a protected word table | Julia | Word Tables | 1 | 01-09-2013 06:04 AM |
Debug for macro run through button only when sheet protected | leahca | Excel Programming | 0 | 11-24-2011 04:47 AM |
Adding table lines to protected form | razberri | Word Tables | 2 | 10-27-2010 05:58 PM |