Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Thread Tools Display Modes
Old 09-07-2014, 07:03 AM
Catalin.B Catalin.B is offline Autoexpand Table on Protected Sheet Windows Vista Autoexpand Table on Protected Sheet Office 2010 32bit
Autoexpand Table on Protected Sheet
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default Autoexpand Table on Protected Sheet


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

   Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False, UserInterfaceOnly:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowInsertingRows:=True, AllowSorting:=True, _
        AllowFiltering:=True, AllowUsingPivotTables:=True
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.
Attached Files
File Type: xlsm Autoexpand table on protected sheet.xlsm (19.9 KB, 42 views)
Reply With Quote

Thread Tools
Display Modes

Similar Threads
Thread Thread Starter Forum Replies Last Post
Autoexpand Table on Protected Sheet 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

All times are GMT -7. The time now is 05:33 AM.

Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd. is not affiliated with Microsoft