Today I did the following:
- Created a new workbook
- Added a pivot table, based on an Access database connection
- Created it using the following setup
After doing that, I grouped my months by year, and dropped in the following code to the Sheet1 module:
[vb]Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Application.EnableEvents = True
End Sub[/vb]Then I changed the value of B1 to one of my account numbers andâ€¦ BOOM!
And when it says the Object invoked has disconnected from its clients, it really means it! What's really bizarre is that hitting end leaves me able to move around the worksheet using the mouse, as evident in the formula bar, but the active cell on the worksheet never changes. Closing and re-opening the workbook has no effect, as the issue persists. The only way to cure it is to shut down Excel and restart.
As it turns out, the problem is that I passed the pivotfilter a number, not a text string. An easy fix with CStr to convert it, and now I can quickly check the balances in my accounts over the last few years:
What really strikes me as really bizarre is that passing a number to a string variable in VBA will not cause an error. The value is accepted just fine. If I'd had to guess about this, I would have expected it to work the same way or, at the very worst, throw a trappable error. Passing a string to a numeric field shouldn't cause such as drastic automation error that forces you to close Excel to cure it.
Suspicious that this might be something to do with my setup, I also tested this on a pivot table created on data from within the workbook (I mocked up a simple three line table) and the same thing happens.