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

 

[6StringJazzer]

Start by showing us the code. (I recommend you select the code then click the VBA button to preserve formatting.)

 

[Mrock1]

This is the essential code:
In the sheet module:
Private Sub Worksheet_Change(ByVal Target As Range)
if Not Intersect(Target, Range(“Control_Type_Filter”)) Is Nothing Then
if Target.Count = 1 Then
Call ReapplyFilters
End If
End if
End Sub

In Module1
Sub ReapplyFilters()
Activesheet.ListObjects(“MyTable”).Range.AutoFilter Field:=6, Criteria1:=“TRUE”
End Sub

The cell in field 6 is True if another field = the value specified in the cell Control_Type_Filter which is outside of and above the table, MyTable.
=if([@[Control Type]]=Control_Type_Filter, True, False)
Again this is a simplified formula for debugging purposes and then formula usually contains other code but this is the essential code.
MyTable starts at row 19 and the range returned by the ListObjects.Range is correct as I put a MsgBox there to display the address.

Ordinarily there is more code in both Worksheet_Change and ReapplyFilters but I whittled it down to just this during my debugging process.

The situation as previously explained is that the cell in Control_Type_Filter has an data validation list (K,S,All). If I select from the dropdown the code runs but the AutoFilter command has no effect. If, however, I manually enter a valid value into the cell, the code runs and the AutoFilter takes effect.

Some research suggests Thai may have been a bug in a much earlier version of Excel from years ago.

 

[6StringJazzer]

Your code refers to named range Control_Type_Filter. What is the range this refers to? What is the address of the "cell above"?

These sentences from your first post are confusing:
"manually enter a valid value in the cell......select a value for the cell above from its data validation dropdown list"
Are you talking about typing a value into the cell vs. clicking on the arrow to open the dropdown list in that same cell? (Either way you are "manually entering a value" which is why I find this confusing.)

Worksheet_Change should behave the same way regardless of whether the cell changed by selecting a value from a data validation dropdown list, or by typing a valid value into the cell.

Since the behavior you are describing shouldn't happen, I expect there is something else in play that you don't realize is having an effect, so you haven't described it. Is there any way for you to share your actual file with us (Google Docs, Dropbox, OneDrive, etc.)?

 

[Mrock1]

Your code refers to named range Control_Type_Filter. What is the range this refers to? What is the address of the "cell above"?

These sentences from your first post are confusing:
"manually enter a valid value in the cell......select a value for the cell above from its data validation dropdown list"
Are you talking about typing a value into the cell vs. clicking on the arrow to open the dropdown list in that same cell? (Either way you are "manually entering a value" which is why I find this confusing.)

Worksheet_Change should behave the same way regardless of whether the cell changed by selecting a value from a data validation dropdown list, or by typing a valid value into the cell.

Since the behavior you are describing shouldn't happen, I expect there is something else in play that you don't realize is having an effect, so you haven't described it. Is there any way for you to share your actual file with us (Google Docs, Dropbox, OneDrive, etc.)?

The named range in this case is D18 with the table being $A19:$BW595 (this includes the header row at 19). And, yes, manually entering refers to typing (manual, by hand) and pressing the enter key as opposed to selecting from the data validation dropdown selection (which I’ve disabled for the time being as a workaround).

And yes it should not be happening and may suggest that some other routine has been triggered to cause the situation. But rest assured that not only does the AutoFilter command line not work when the cell value is changed using the DV dropdown selection, but none of my breakpoints or STOP statements work either. Frankly this to me suggests either a bug or a subtle corruption in the workbook. As to it being a bug; anecdotally it seems such a bug did exist a few years ago in versions not O365 but I can’t find any offical confirmation of that, just people reporting experiencing problems.

As to other routines being triggered, ithet than Worksheet_Change, I only have _Activate, _BecoreDoubleClick, _BeforeRightClick, _Deactivate, and _SelectionChange none of which are triggered by changing the selected cell’s value. I can confirm the code runs as I’ve included msgbox commands before and after the AutoFilter command that do appear.

I saw a post somewhere that suggested a corruption in the sheet or other module but rebuilding the workbook is complicated due to the combination of formulas formatting and conditional formatting.

Unfortunately I can’t upload the original workbook as it contains a lot of proprietary and sensitive information as it is used live.

I will tried to simulate the condition in a simple workbook which didn’t fail

I also tried a separate subroutine with just the AutoFilter command that worked.

What I have noticed is that my worksheet takes a long time to recalculated, like due to the number of formulas. Is it possible that the autofilter command is just occurring to soon before the recalculation has completed? Is it somehow possible to wait for the recalculation and only then execute the autofilter command?

 

[Mrock1]

Maybe a little more information. Thinking that the calculation hadn’t complete I included a loop routine to wait for the application.calculationstate to equal 0 or xlDone. However, I found that it remains at 2, xlPending, which according to MS means: “Changes that trigger calculation have been made, but a recalculation has not yet been performed”.

It just doesn’t go away. Alway 2 and never 0 (xlDone). All quite weird considering the calculation visually appears to be complete.

 

[Mrock1]

Ok, the application.calculationstate always returning 2, pending, may be because I do use volatile functions, like Offset, in my formulas.

However empirically speaking, there appears to be a difference in how excel handles a direct cell entry and how it handles a cell being updated by a data validation dropdown. Having said that I won’t exclude the possibility or some subtle corruption or that my workbook has just become so complex that I’ve somehow manifested a bug. My workbook uses a lot of array formulas and Let functions some of which are quite complex and take many steps to evaluate.

 

[6StringJazzer]

I am unable to reproduce your problem based on your description, so I can't be of any further help. There may be a difference but I could not reveal it in my test file.

 

[Mrock1]

I am unable to reproduce your problem based on your description, so I can't be of any further help. There may be a difference but I could not reveal it in my test file.

Personally I think that I’ve reached some threshold on my workbook the table I have it some 600 rows and there are many array formulas as well as alt nose in condition formatting. I recently added some new summation formulas (also array) based in the table and notice the the recalculation started to noticeably pause whenever I updated a cell in the table. I do know that the data validation cells I am now having trouble with used to work and they only recently started to have problems. So empirically I’d suggest that the worksheet has now become so complex as to cause problems, or by some chance the sheet has become corrupt.

Is there an easy way to build a replacement sheet that doesn’t require adding all the elements (cell formulas, data validation, conditional formulas underlying VBA, etc). For instance copying the sheet, which I presume would copy over everything, but would it copy over subtle corruptions as well?

 

[6StringJazzer]

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.