Solved: How to run a macro when user clicks a value in a dropdown box (form control, not UFM)

[Jaspington]

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!

[mikerickson]

If the control is from the Forms menu, rather than an ActiveX control, it can be assigned to a macro that has code like

With ActiveSheet.Shapes("Drop Down 1").ControlFormat
If .Value = 0 Then
MsgBox "nothing Selected"
Else
MsgBox .List(.Value) & " is the " & .Value & "th item from the list."
End If
End With

[Jaspington]

Hi Mike

That looks like just the ticket - thanks, I'll give that a try, looks like it should do just what I need

:thumb

[Jaspington]

Hi again Mike

Just tried this code, and it works when it is run within a routine, to check the value and then perform a given action.

But what I need it do is to run this every time the user changes the dropdown, to check the value.

Can I somehow use this code to do that, as I am not sure how you call this code with every change of the dropdown?

[mikerickson]

Right click the control and choose Assign Macro from the dropdown, then assign your macro to that control and it will run every time the user changes the value of the Combo Box.

[Jaspington]

Right click the control and choose Assign Macro from the dropdown, then assign your macro to that control and it will run every time the user changes the value of the Combo Box.

Works like a charm, exactly what I need.

Thanks Mike, help greatly appreciated

[mikerickson]

I'm glad it worked for you.