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

[KrenVT]

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

Sub transferAccessExpenseData()
   
    Dim dataRaw As Worksheet 'This is the Raw Expense Data sheet
    Dim dataExpense As Worksheet 'This is the Itemized Expenses sheet
    Dim raw_tbl As ListObject 'This is tblExpenseRaw on the Raw Expense Data sheet
    Dim expense_tbl As ListObject 'This is tbl_ItemizedExpense on the Itemized Expenses sheet
    Dim dataRange As Range 'This is the range of rows that are visible after filtering tblExpenseRaw

    Dim getProjectCode As String 'Holds the project code for filtering tblExpenseRaw
    Dim vCount As Long 'This variable holds the count of the number of visible rows in tblExpenseRaw

    Dim RC As Long 'Variable for counting row numbers to format for tbl_ItemizedExpense
    Dim LastRow As Long 'Variable to get last row for tbl_ItemizedExpense
        
    Set dataRaw = Sheets("Raw Expense Data")
    Set dataExpense = Sheets("Itemized Expenses")
    Set raw_tbl = dataRaw.ListObjects("tblExpenseRaw")
    Set expense_tbl = dataExpense.ListObjects("tbl_ItemizedExpense")
    Set dataRange = Sheets("Raw Expense Data").Range("tblExpenseRaw")

Application.ScreenUpdating = False

Worksheets("Raw Expense Data").Activate
ActiveSheet.Unprotect ("gme")
 
'Initializing the project code variable.
    getProjectCode = Sheets("Raw Expense Data").Range("C2").Value
    
'Filter the imported Access expense raw data by project code which is located in column 10
    With ActiveSheet.ListObjects("tblExpenseRaw").Range
        .AutoFilter
        .AutoFilter Field:=10, Criteria1:=getProjectCode

    End With

'Need to count the number of visible rows in tblExpenseRaw and store in vCount variable.
    With dataRange
          For Each rngArea In .SpecialCells(xlCellTypeVisible).Areas
            vCount = vCount + rngArea.Rows.count
        Next
  
    End With

MsgBox "vCount (visible rows) is " & vCount

Worksheets("Itemized Expenses").Activate
ActiveSheet.Unprotect ("gme")

'Clear out any data in tbl_ItemizedExpense and then resize the table using vCount
    With expense_tbl
        .AutoFilter.ShowAllData
        .DataBodyRange.Clear

'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
'ERROR BELOW
        .Resize .Range.Resize(vCount + 1, .ListColumns.count)
    
  End With
    
Worksheets("Raw Expense Data").Activate
 
'Need to copy the visible rows in tblExpenseRaw and paste in the tbl_ItemizedExpense.
'Since the range is already selected, just need to copy.
    dataRange.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy

    Worksheets("Itemized Expenses").Activate
    Sheets("Itemized Expenses").Range("B6").Select
    Selection.PasteSpecial xlPasteValues

    Application.CutCopyMode = False
     
'-----------------------------------------
'------------------------------------------------
'Format rows in tbl_ItemizedExpense
 
'Data starts on row 6 of the Itemized Expenses sheet.    
     RC = 6
 
'Using column 2 because every record must have an Expense ID    
     LastRow = Cells(Rows.count, 2).End(xlUp).row
     
'Using the For Next to go through each row of tbl_ItemizedExpense.
'Need the -1 so the total row to excluded
    For RC = 6 To LastRow - 1
     
'Expense ID, Expense Type, GL Code, Funding Source, Comments fields format
    Range("B" & RC & ":F" & RC).Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
 'Unlock Expense Type, GL Code, Funding Source, Comment
    Range("C" & RC & ":F" & RC).Select
    With Selection
        .Locked = False
    End With

'Expense Amount field format
    Range("G" & RC).Select
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
        .Locked = False
    End With
    Selection.NumberFormat = "#,##0.00"

'Transaction Date format
    Range("H" & RC).Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
        .Locked = False
    End With
    Selection.NumberFormat = "m/d/yyyy"

'Notes field format
    Range("I" & RC).Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlTop
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
        .Locked = False
    End With

  'Fiscal Year, Project Code, and Transaction Month
    Range("J" & RC & ":L" & RC).Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlTop
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
        .Locked = True
    End With
 Next RC
    
Sheets("Itemized Expenses").Range("C6").Select
    
    ActiveSheet.Protect ("gme")

       If ActiveSheet.Protection.AllowFiltering = False Then
       ActiveSheet.Protect AllowFiltering:=True
        
    End If
    
  Application.ScreenUpdating = True
    
    
 End Sub

[BadlySpelledBuoy]

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

[KrenVT]

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

[BadlySpelledBuoy]

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

[KrenVT]

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

With Worksheets("Itemized Expenses")
        .Visible = True
        .Activate
        .Unprotect "gme"
        'Clear out any data in tbl_ItemizedExpense and then resize the table using vCount
        With .ListObjects(1)
            .AutoFilter.ShowAllData
            .DataBodyRange.Clear
            .Resize .Range.Resize(vCount + 1, .ListColumns.Count)
        End With
        .Protect "gme", UserInterfaceOnly:=True
    End With

Karen's Code

Worksheets("Itemized Expenses").Activate
ActiveSheet.Unprotect ("gme")

    With expense_tbl
        .AutoFilter.ShowAllData
        .DataBodyRange.Clear
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
'ERROR BELOW
        .Resize .Range.Resize(vCount + 1, .ListColumns.count)
    
  End With

[BadlySpelledBuoy]

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

[KrenVT]

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

[Artik]

There are some actions that unfortunately require the sheet to be unprotected despite UserInterfaceOnly = True. In this example, the line will cause the error:

        'Clear out any data in tbl_ItemizedExpense and then resize the table using vCount
        With .ListObjects(1)
            .AutoFilter.ShowAllData
            .DataBodyRange.Clear
            .Resize .Range.Resize(vCount + 1, .ListColumns.Count)
        End With

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

[BadlySpelledBuoy]

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