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.

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

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

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

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.

Originally Posted by KrenVT

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

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

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

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