VBA Works Differently for Data Validation List Selection over Manual Entry

Mrock1

Board Regular
Joined
Oct 7, 2014
Messages
71
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

Board Regular
Joined
Oct 7, 2014
Messages
71
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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Mrock1

Board Regular
Joined
Oct 7, 2014
Messages
71
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

Well-known Member
Joined
Jan 27, 2010
Messages
2,541
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Glad it worked out, though we will probably never know exactly what was wrong.
 

Forum statistics

Threads
1,181,673
Messages
5,931,346
Members
436,787
Latest member
ogharipour

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top