Marco using drop down menu

[John252058]

I'm a newbie using VBA. I have a spread sheet to create a cultist for parts. Within this spreadsheet I have several macros to copy and paste information to the cultist page.
I would like to create a macro that will run the appropriate macro by selecting the proper macro name from a drop down menu then click the button.

 

[My Aswer Is This]

What is a:

drop down menu​

?

Do you mean a data validation list.

And where will this data validation list be?

 

[John252058]

Yes, correct its a data validation list and will be on the same page as the macro button.

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window


Assuming your drop down list is in column A
Modify script if needed
Try this:

When you double click on a macro name in column A that macro will run

So if you double click on George the script named George will run

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
On Error GoTo M
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Cancel = True
Dim ans As String
ans = Target.Value
Application.Run Target.Value
End If
Exit Sub
M:
MsgBox "There is no sub named  " & ans
End Sub

 

[JackDanIce]

Try:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Target.Count * Target.Column = 1 Then
    Cancel = True
    On Error GoTo ExitMe
    Application.Run Target.Value: Exit Sub
End If

ExitMe: MsgBox "Macro: " & Target.Value & " not found", vbExclamation, "Macro Not Found"

End Sub

 

[Fluff]

As you're using a DV list another option would be

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address <> "$A$1" Then Exit Sub
   On Error GoTo Xit
   Application.Run Target.Value
   Exit Sub
Xit:
msgbox "Macro " & Target.Value & " not found"
End Sub

This assumes your DV dropdown is in A1

 

[John252058]

Thanks folks, I'll try your suggestions. I'd post the file but not sure how yet, new to the forum.

 

[John252058]

Just to explain. My data validation list contains the following #'s 1000, 1100, 1200, 1300, 1400, 1500, 2000, 2100, 2200, 2300, 2400, 2500, the list located in C12 of Sheet1, each model # has its own macro to copy and paste the cutlist to the cutlist sheet then returns to Sheet1. I want to select the appropriate model# from the data validation list then click the button then macro uses the appropriate macro assigned for the model selected. Again Thanks folks for all your help

 

[My Aswer Is This]

So how do we know what Macro is assigned to

1100 ? Or any of the other numbers?​

 

[My Aswer Is This]

Assuming you have macro names in your Data Validation list in Range("C12")
Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("C12")) Is Nothing Then
'Modified 5/26/18 8:13 AM EDT
On Error GoTo M
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim ans As String
ans = Target.Value
Application.Run Target.Value
End If
Exit Sub
M:
MsgBox "There is no sub named  " & ans
End Sub