Jaspington
10-29-2011, 11:07 AM
Hi all
I’m trying to find a way of running a macro when a user clicks a value in a drop-down box on a sheet (ie a form control on the sheet, not within a VBA user form). I have tried using the worksheet change event on the linked cell for the drop-down, but a change event is not triggered in this instance.
I have a workaround whereby I put the link cell on its own worksheet, and have a vlookup to a table that returns the actual value clicked (ie if the 2nd value in dropdown is “Mike” then the link cell returns “2”, and I have a vlookup that looks up 2 and returns “Mike”). So when the user clicks/changes the dropdown, the linked cell changes, and because of the vlookup the worksheet recalculates, so the worksheet calculate event triggers – and I use this event to run my macro.
But I actually have 4 drop-downs, so I need to do this on 4 separate sheets, so it is not ideal, and my solution is somewhat cumbersome
So I was wondering if there was a better way of doing this? Ideally I guess I am looking for an event that fires when the drop-down is clicked, but I can’t seem to find it. I know I could build a user-form to do this, but at this stage, for various reasons, I'd rather not.
Thanks in advance for any pointers!
I’m trying to find a way of running a macro when a user clicks a value in a drop-down box on a sheet (ie a form control on the sheet, not within a VBA user form). I have tried using the worksheet change event on the linked cell for the drop-down, but a change event is not triggered in this instance.
I have a workaround whereby I put the link cell on its own worksheet, and have a vlookup to a table that returns the actual value clicked (ie if the 2nd value in dropdown is “Mike” then the link cell returns “2”, and I have a vlookup that looks up 2 and returns “Mike”). So when the user clicks/changes the dropdown, the linked cell changes, and because of the vlookup the worksheet recalculates, so the worksheet calculate event triggers – and I use this event to run my macro.
But I actually have 4 drop-downs, so I need to do this on 4 separate sheets, so it is not ideal, and my solution is somewhat cumbersome
So I was wondering if there was a better way of doing this? Ideally I guess I am looking for an event that fires when the drop-down is clicked, but I can’t seem to find it. I know I could build a user-form to do this, but at this stage, for various reasons, I'd rather not.
Thanks in advance for any pointers!