macros

[edward_laton]

Hello everyone
Is there a way to use dropdown menus to run
a macro, or is there a way to use VB to do this.

Any help would be helpfull

Ed

 

[Ekim]

1. On say Sheet1, select cell A1 (colour the cell say yellow and put a border around the cell to highlight it).
2. Go to Data/Validation.
3. In the "Allow" dropdown menu select "List".
4. In the "Source" box type "Print", "Copy", "Close" (without the apostrophes but include the commas between the expressions). For the sake of this demo, I am assuming that you have a print, copy and close macro (change to whatever).
5. OK.
6. Alt F11 and enter the following macro in the sheet1 module:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 1 Then Exit Sub
If Target = "Print" Then Macro1 ' Change to name of your first macro
If Target = "Copy" Then Macro2 ' Change to name of your second macro
If Target = "Close" Then Macro3 ' Change to name of your third macro
End Sub

7. To test the above, put the following macros in a normal module (in the VBA Editor - Insert menu / Module):

Sub Macro1()
MsgBox "Hello - Print macro"
End Sub

Sub Macro2()
MsgBox "Hello - Copy macro"
End Sub

Sub Macro3()
MsgBox "Hello - Close macro"
End Sub

8. Save the file.
9. Return to sheet1, activate cell A1 and make your selection from the drop down menu.


Regards,

Mike