VBA Works Differently for Data Validation List Selection over Manual Entry

[Mrock1]

Hi
I’ve run into a weird situation. I have a worksheet with a table and a cell above to select a filter key. I have a Worksheet_Change function that detects if this cel has been changed and runs another Module1 subroutine. This the way it all works is that the table contains a column which uses the value in the cell above to determine true or false. The called subroutine just attempts to Apple auto filter of the table to that field:=“True”.

What I find is the following:
If I manually enter a valid value in the cell above it all works correctly and the table is filtered properly.
If, however, I select a value for the cell above from its data validation dropdown list, the table field calculates correctly but the filter command has no effect.
Worse still is that in this situation the vba code ignores any breakpoints I set. I included msgbox commands to confirm the subroutines are running correctly.

So my only workaround is to manually enter the key value in the cell above the table. But I don’t think this should be happening.

It is unlikely but possible that the code module(s) have become corrupted somehow but everything looks ok. And as I said it all works if I manually enter a valid value in the cell above the table.

I use O365

Any ideas would be gratefully accepted.

Thx
Max

 

[Mrock1]

I am not an expert on Excel corruptions. I would try the easiest thing first, which would be a wholesale copy of each sheet to a new file by right-clicking the tab and using move or copy. That will copy data, formulas, formatting, VBA, named ranges, everything. IME that is enough to leave a corruption behind. But I can't guarantee it. Certainly worth the effort to try, assuming we are talking about a small number of sheets.

I’ll give that a go. After all it likely can’t make things worse. Thx

 

[Mrock1]

I’ll give that a go. After all it likely can’t make things worse. Thx

Well. I did that - copied everything to a new workbook. Apart from the fact that not all my named ranges copied over and I spent a long time correcting the links to external workbooks, it’s now working properly. I have to say that I only copied the two of the worksheets that i needed. The others were a long time collection of similarly complex worksheets for other purposes.
I’m can only theorise that either the “corruption” was indeed corrected, or that by removing the other worksheets I shortened the recalculation time enough to prevent the problem from manifesting.

So now it’s a case of of ain’t broke, don’t fix it.

All good and thanks for the advice.

Max

 

[6StringJazzer]

Glad it worked out, though we will probably never know exactly what was wrong.