I'm crashing Excel with AutoFilter.Apply

[lucky_sevans]

I'm relatively new to Excel and VBA, so please be patient if I screw up terminology. I've had many questions answered here over the last month while working on this project so it seemed the best place to ask for help. This may not be necessary but for context it is a project for my sister who is a real-estate agent. The application is tracking the inventory she uses for staging. The particular sheet I am having issue with is a table which contains several fields and is used for her to choose items from her inventory to utilize for a new listing. The filtering is used to "Narrow down" the items to view just the items she is interested in at that moment. There is a field "Category" which could be "Furniture", "Appliances", "Wall Hangings", "Plants", etc. and I have created a Slicer for this field. The other field of interest is called "Rooms" and is a list of room abbreviations the item is appropriate for. A toaster probably will only have "KI" in this field while a Bar Stool may have "KI/DI" in this field. A large framed painting could very well have "DI/LV/BD" and a houseplant could have "*" in the field.

Since I could not use this field as is for a slicer, I created an "Admin" table on my Admin sheet with just room abbreviations that contains just 2 fields, each abbreviation and

[B]=SUBTOTAL(3, $A1)[/B]

(A trick I picked up here which is used to indicate that the row is visible). Thus I have a slicer on this sheet, which references the table on the Admin sheet. In order to make this work I have a "helper" column in my INVENTORY table with the formula

[B]=OR([@Rooms]="*",IF(TEXTSPLIT([@Rooms],"/")=IF(tblROOMS_FOR_SLICER[Column2],tblROOMS_FOR_SLICER[Column1]),1,0))[/B]

. This works and I get an appropriate TRUE/FALSE answer in the Helper1 field. The table has autofilters for NumAvail (> 0) and Helper1 (=TRUE).

The issue is if the user de-selects any rooms, that is properly reflected in the Helper1 column (the value changes to FALSE) but the table is not re-filtered. If the user clicks on any filter button and clicks OK the table redraws correctly. But obviously I want the pseudo-slicer to act like a slicer. It seems that the only event that fires is Worksheet_Calculate so I placed the code:

Private Sub Worksheet_Calculate()

   ' Application.ScreenUpdating = False
    Sheets("InventoryPick").Shapes("MyTextBox").TextFrame.Characters.Text = Range("A1").Value
    With ActiveWorkbook.Worksheets("InventoryPick").ListObjects("tblInventory")
        .AutoFilter.ApplyFilter
    End With
End Sub

And this kills Excel almost instantly. I've realized that the _Calculate event fires many times (my guess is once for each row displayed) but I think its the best or only event that is triggered in this circumstance.

Hopefully I've explained this well and hopefully I haven't missed a much more obvious way to do what I need. The only additional things I can think of to add is to ignore the fact that there is a "Kitchen" Category (as well as a KI room) and don't question why wire chairs would be in a kitchen. I've tried a bunch of other things, including re-setting the AutoFilters for the fields (since they don't use 'ApplyFilter' explicitly and because I noticed that when I look at the Filter dropdown when a row with FALSE is displayed, False is Selected in the UI (But not when I use Custom > equals TRUE)) and that doesn't crash but produces some other obscure error. Anyway, I'm hoping someone can help me out of the weeds here.

 

[Anthony47]

Try adding Application.EnableEvents =False /True as follows:

Application.EnableEvents=False     'ADDED Here
    Sheets("InventoryPick").Shapes("MyTextBox").TextFrame.Characters.Text = Range("A1").Value
    With ActiveWorkbook.Worksheets("InventoryPick").ListObjects("tblInventory")
        .AutoFilter.ApplyFilter
    End With
Application.EnableEvents=True           'ADDED Here