Microsoft Office Forums Autoexpand Table on Protected Sheet

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
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
Expert
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

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!
Attached Files
File Type: xlsm Autoexpand table on protected sheet.xlsm (19.9 KB, 35 views)
Reply With Quote
Reply

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
Autoexpand Table on Protected Sheet 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 07:50 AM.


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