+ Reply to Thread
Results 1 to 9 of 9

Error Table Features Aren't Available - Sheet Protected - Frustrating

  1. #1
    Registered User
    Join Date
    07-31-2012
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    30

    Error Table Features Aren't Available - Sheet Protected - Frustrating

    Hi, Really struggling with why I am getting the error 1004 "Table features aren't available because the sheet is protected." The code unprotects the sheet. I also manually unprotected both the Itemized Expenses and Raw Expense Data sheets and still get the error. What is so frustrating is I use the same code (editing names as needed) for transferring budget data and it works great.

    Greatly appreciate any help with this.

    Karen

    Please Login or Register  to view this content.

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    6,221

    Re: Error Table Features Aren't Available - Sheet Protected - Frustrating

    Any chance we could see a desensitized version of the file? Far easier to diagnose the issue when we can see it in context.

    BSB

  3. #3
    Registered User
    Join Date
    07-31-2012
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: Error Table Features Aren't Available - Sheet Protected - Frustrating

    Hi,
    Attached is a desensitized version of the file. Currently the Sub "transferAccessExpenseData" (Module 1) runs from VBA Editor. The Sub "verifyTransactionDate" in Module 2 is called when changes are made to the cells in the Transaction Date column of tbl_ItemizedExpense. Wondering if this is my issue.
    Karen
    Attached Files Attached Files

  4. #4
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    6,221

    Re: Error Table Features Aren't Available - Sheet Protected - Frustrating

    In the attached I've slightly rewritten your code and got rid of most of the unprotect/protect lines.
    For some reason the section for manipulating the "Itemized Expenses" sheet still needs to be unprotected first, despite the use of 'UserInterfaceOnly:= True' in the workbook open event.
    I'm not sure why this is happening as couldn't find a reason for it, but I'm sure it's something obvious I'm missing.

    Anyway, give it a try and see if it does what you expect.

    BSB
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-31-2012
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: Error Table Features Aren't Available - Sheet Protected - Frustrating

    It WORKS! Thank you!

    Very interesting. Below is a comparison of the code. I have 3 questions.

    1. You used .ListObjects(1) instead of expense_tbl that I used. Is there a reason you used the .ListObjects(1) instead of expense_tbl??
    2. In my code I activate the Itemized Expenses sheet and then unprotect it. Your way is much better but seems like the way I did the code should also work.
    3. Is your code working because you put the With .ListOjects(s) End With that does the resizing inside of the With Worksheets("Itemized Expenses") End With?

    BSB's Code

    Please Login or Register  to view this content.
    Karen's Code

    Please Login or Register  to view this content.

  6. #6
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    6,221

    Re: Error Table Features Aren't Available - Sheet Protected - Frustrating

    Quote Originally Posted by KrenVT View Post
    1. You used .ListObjects(1) instead of expense_tbl that I used. Is there a reason you used the .ListObjects(1) instead of expense_tbl??
    2. In my code I activate the Itemized Expenses sheet and then unprotect it. Your way is much better but seems like the way I did the code should also work.
    3. Is your code working because you put the With .ListOjects(s) End With that does the resizing inside of the With Worksheets("Itemized Expenses") End With?
    1. This really comes down to personal style. If there is only one table on a sheet I always refer to it as .ListObjects(1) as if I ever need to change the table name I don't have to update the code as .ListObjects(1) will work regardless of the table name.
    2. Your way should also work as effectively we're doing the same thing in both our codes. I also activate the sheet then unprotect it, just with a slightly different method to yours.
    3. The only reason I put the ListObjects with block inside the WorkSheet with block is again personal style. It saves having to refer to the sheet name again (I'm very lazy with my typing of code!) and to me it reads easier. As we're already in a Worksheet with block at that point it's clear in my code (to me anyway) that the ListObject with block must be acting on a ListObject on that worksheet.

    I think a big part of the issues you were having with your code is performing actions such as resizing a table range fires the WorkSheet Change event and in your code the sheet was being protected again at that part, but not protected with the UserInterfaceOnly parameter set to True. By removing all of the unprotect/protect lines it prevented this happening, almost completely. I've still not got to the bottom of why the Itemized Expenses sheet still needs unprotecting, but I will keep looking

    BSB

  7. #7
    Valued Forum Contributor
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    783

    Re: Error Table Features Aren't Available - Sheet Protected - Frustrating

    There are some actions that unfortunately require the sheet to be unprotected despite UserInterfaceOnly = True. In this example, the line will cause the error:
    Please Login or Register  to view this content.
    Other cases known to me (this is an open list, which I supplement when I find such an action):
    .Validation.Add
    .PivotCache.Refresh
    .AddComment
    .FormatConditions


    Artik

  8. #8
    Registered User
    Join Date
    07-31-2012
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: Error Table Features Aren't Available - Sheet Protected - Frustrating

    Greatly appreciate your help and time with this. I am relatively new to Excel VBA and at times the coding can get so frustrating. Individuals like you on this forum are an excellent resource.
    Thanks again.
    Karen

  9. #9
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    6,221

    Re: Error Table Features Aren't Available - Sheet Protected - Frustrating

    More than happy to help. That's what we're here for so don't be shy to ask for help if you get stuck with it. VBA will all click into place eventually.

    BSB

+ 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. Frustrating sheet add problem
    By rocdoc in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-13-2017, 07:22 AM
  2. Replies: 3
    Last Post: 01-29-2016, 03:57 PM
  3. [SOLVED] Error 400 when sheet is protected
    By Master Foo in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-03-2013, 04:07 PM
  4. Excel VBA Frustrating Run-time error '13' Type mismatch
    By shastamccloud in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-08-2010, 02:34 AM
  5. Protected Pivot Table Error on Open
    By yawnzzzz in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-19-2009, 06:51 PM
  6. How do you allow hide/unhide or group/ungroup features while protected
    By caliskier in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 12-24-2007, 07:11 PM
  7. Frustrating Excel error
    By benjammind in forum Excel General
    Replies: 0
    Last Post: 04-21-2005, 04:26 AM

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