Macros via Dropdown list

[Bxl]

Hi all, I have a spreadsheet which has a number of Macro's 1-10 and I am trying to call these macro's using a dropdown list Temp1-10). This is looked up in cell F2 and this is where I try to call the Macro from. I have tried numerous solutions and I just don't seem to be able to get it to work. Any ideas?

Many thanks,
BXL

 

[Dermot]

I suggest you add some code to trigger the correct macro when the selection changes

It seems that F2 changes when D2 changes. So a change in D2 will be the trigger.
The code below runs whenever a cell is changed on the worksheet. If the changed cell is D2, it runs the macro name in F2.

1. give D2 and F2 a range name. I named D2 as selector, and F2 as macro, but you can use different names (just change my code below)
2. Press Alt F11 to get to the VBA code window
3. At left, you will see a list of the sheets in your workbook. Doubleclick the sheet name with D2 and F2 in it
4. This will open an empty code page for that sheet. Paste this code into it

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = Range("selector").Address Then Application.Run Range("macro").Text
End Sub

(If you have used different range names, change the code to use them instead of my names)
5. Close the VBA code window and test it