Can't get worksheet_change to work with Data Validation list

[tianimo]

Hi,

I've set up a macro that is triggered using a worksheet_change(ByVal Target As Excel.Range) sub - this works fine.

However, I am using one cell as my target. I want to be able to trigger the macro by changing the cell value using a drop-down (I've done this with data validation). It seems Excel doesn't recognise this as a cell change, i.e. it doesn't trigger the event.

I know I could do it with a combo box (with a different sub to recognise the event) but I was hoping to avoid using Active X controls. Is there a way of getting round this sticking with the data validation method?

Thanks.

 

[Andrew Poulsom]

What version of Excel are you using? This was a problem with Excel 97, but it has been fixed since.

http://www.contextures.com/xlDataVal08.html#Change

 

[tianimo]

Thanks - I'm using Excel 2007.

I rebooted the machine and rewrote the macro again from scratch, it appears to be working now!