Can you have the macro already in there ? altough writing a macro to write a macro is possible, it is problematic if you or your users have Excel XP or above. It is nearly impossible as well if you need to protect the VBProject. But if you just leave the macro pre-written, then it's very easy, just add the shape and do something like
MyShape.OnAction = "TheNameOfTheMacro"
and that is it.