Can't copy cell containing dropdown list
Posted by Larry Jones on May 24, 2001 4:12 AM
I am trying to write some code to copy the current cell, to the next X rows. I have it working in all cases, except 1!
If the cell being changed is a dropdown list, which points to another range of cells for it's values, it does not trigger the Workbook_SheetChange event when it's changed! Which means, my code does not execute. This happens in both cases where the Data Source is taken from a range of cells. Other cells with dropdown lists work, but in those cases the lists are short and have been typed directly into the Data Validation Source.
For the two cells where this does not work, the Data Validation Source is set to something like =$AY$2:$AY$88.
When I click the dropdown button and choose another value, why doesn't the Workbook_SheetChange event fire for these two, when they do for the other 4 "hard-coded" dropdown lists? What event should I be using?
I am currently using Excel 97 SR-2 on an NT 4 system.
TIA
Posted by Dave Hawley on May 24, 2001 4:35 AM
Hi Larry
You can overcome this by putting a simple =A1 (assuming A1 is the cell with validation) in any cell then use that cell as your Target.
Dave
OzGrid Business Applications
Posted by Larry Jones on May 24, 2001 4:53 AM
I tried that, but it didn't work.
Column G has a data validation source of =$AY$2:$AY$88. In cell P2 (for a test) I put, =G2, and it did get the value of G2. But, then when I changed G2, using the dropdown, P2 did not change.
I have to find what even (if any) fires when the dropdown in G2 is used.
Posted by Larry Jones on May 24, 2001 5:19 AM
It appears that this is a documented bug in Excel 97 (Q172832 in the knowledge base) but now I can't get the workaround to work! :(
