+ Reply to Thread
Results 1 to 4 of 4

Locking Table Columns Isn't Working

  1. #1
    Registered User
    Join Date
    09-25-2008
    Location
    Winnetka, CA
    Posts
    83

    Locking Table Columns Isn't Working

    (Actually, the title should be: UNlocking Table Columns Isn't Working)

    I have a table where I want certain columns protected and other columns unprotected. I used an instruction like this:

    Please Login or Register  to view this content.
    But after it runs, the cells there are still locked. I experimented by adding some Watches, and discovered that this could happen:

    Range("Table1[[#ALL],[Column1]:[Column4]]").Locked FALSE
    Range("A1:A5").Locked TRUE

    Yes, A1:A5 is within the columns of the table that should be unlocked. So those cells can be locked although the column is not.

    Furthermore, I tried
    Please Login or Register  to view this content.
    which should have locked all the cell in the column, but I wound up with this in the Watch window:

    Range("Table1[[#ALL],[Column1]:[Column4]]").Locked NULL

    In other words, setting the Locked property of table columns doesn't actually do anything.

    I wanted to identify the columns because I'm using this macro on different tables with different numbers of rows. But it looks like I'll have to identify those columns as an ordinary range.

    What's the best way to do that?
    Last edited by shawnvw; 08-14-2015 at 02:51 PM.

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Locking Table Columns Isn't Working

    Hi there,

    The code you posted seems to work correctly on my Excel 2013 installation.

    Your code will operate on Table1 of whichever worksheet happens to be active when the code is executed - is this what you want? If not, you should use a fully qualified reference i.e.:

    Please Login or Register  to view this content.

    Hope this helps - please let me know how you get on.

    Regards,

    Greg M

  3. #3
    Registered User
    Join Date
    09-25-2008
    Location
    Winnetka, CA
    Posts
    83

    Re: Locking Table Columns Isn't Working

    Quote Originally Posted by Greg M View Post
    Hi there,

    Your code will operate on Table1 of whichever worksheet happens to be active when the code is executed - is this what you want? If not, you should use a fully qualified reference i.e.:

    Please Login or Register  to view this content.
    I think you're right, but not the way you think!

    What I say is "Table1" in my example has a different name on each sheet, so I was accessing the wrong table.

    Now this seems to work:

    Please Login or Register  to view this content.
    Thanks!

  4. #4
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Locking Table Columns Isn't Working

    Hi again,

    Many thanks for your feedback.

    Glad to have helped, even if only indirectly!

    Best regards,

    Greg M

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Locking cells on certain columns
    By Shadmani in forum Excel General
    Replies: 2
    Last Post: 01-25-2013, 12:06 AM
  2. Locking columns based on calculated cell values in columns across a sheet.
    By andyr826208 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-04-2012, 05:17 AM
  3. Locking columns
    By Cmorgan in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 11-14-2011, 03:29 AM
  4. Locking columns
    By helen_spencer9 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-24-2007, 05:45 PM
  5. Locking Columns
    By kathemius in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-17-2007, 02:43 PM
  6. Locking columns
    By mattantu in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 10-11-2006, 07:44 AM
  7. Locking columns
    By chefatl in forum Excel General
    Replies: 1
    Last Post: 03-15-2006, 08:25 PM
  8. [SOLVED] Locking a table
    By Ket in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-15-2005, 12:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1