How can I place macro names in cells and launch them
I would like to have a a macro to run when I click on its vba name's value in a cell without using a button or a keyboard shortcut. For instance, A1="Macro1" which is the actual name of the macro. I want to click on A1 and macro1 runs.
thanks for your help.
You could do that for a double-click by writing a piece of code in the "Worksheet_BeforeDoubleClick" event handler.
If you want to write a general piece of code, not being too specific, you may find the "CallByName" function useful.
Copy your cell ( your macro name ) as a picture into the same place.
Then you can assign a macro to the picture ( right click the mouse )
Thank you for the ideas - I will have tens and tens of macros for different reports etc. It seems like the pictures might be timeconsuming vs just typing in the actual macro name in a cell. What about the Eval function? If that would work, could someone give me a few lines of code as a basis to start? I'm just a teacher and not as smart in VBA as many of you. thanks
This code does the job:Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) On Error Resume Next CallByName ActiveSheet, Target.Value, VbMethod
Sub test1() MsgBox "test1"
Sub test2() MsgBox "test2"
Sub test3() MsgBox "test3"
Paste this code on the module associate to a form.
Write "test1", or "test2", or "test3" is some cells of this form.
When you double-click that cell it will run the corresponding query.
Thank's. Your code works perfectly.
I will save it for further use.
Thank you Lalbatros for the code. Maybe I don't understand how it will work but I don't want to use a "Form" but want to enter the actual macro names into cells on the spreadsheet and when I double click on that cell, then the macro runs. Maybe what you gave me will do that but with my limited knowledge it doesn't appear so. Is there a way to modify your code to do what I need or do we need to start over? Thanks
The code will work just as you want it to.
This is how you can use it:
Select you worksheet in the list
Insert - > Module.
Then paste the Code into this module. ( Ctrl+C and Ctr+V )
You have to have your macro's as well in this worksheet module. Not in the ThisWorkbook module
Then you just write you macronames somewhere in your worksheet, double click the cell concerned and WAU
Thank you for helping: I followed your instructions exactly but I couldn’t get it to work.
1.opened a new workbook with 3 sheets – stayed on sheet 1
2.went to vbe by alt-f11 – didn’t have to select sheet one as it was already highlighted
3.Off menu clicked on Insert > module : module 1 then came up and was highlighted
4.Copied the code from above by lalbtros and pasted it into the white space in Mod 1
5.Copied the name without the () (tried it with and didn’t work either)
6.Went back to sheet 1 and pasted the name
7.doubled clicked on it and the message box didn’t come up
8 tried it by putting the same code in this workbook too - but didn't work
What do you think I did wrong?
Sorry , It did work! Thank you one and all. You guys are super. I forgot to place it in the sheet as well as in the module. thanks again