Microsoft Excel - Assign macro to a button in another workbook through VBA

Asked By Pete Bradshaw on 21-May-13 08:00 AM
Hi Guys,

This is driving me mad and I'm sure it should be fairly straight forward.

Using VBA, I'm lifting two sheets from a master workbook and saving them. One of the worksheets in the new workbook has some code in it and I'm trying to assign this code to a button on the other sheet.

The problem I'm having is that no matter what I do, the code will try to run from the parent workbook.
(The new workbook is the active workbook).

myShape.OnAction = ActiveWorkbook.Name & "!Sheet1.ShowRows"
myShape.OnAction = ActiveWorkbook.Path & "!Sheet1.ShowRows"

Has anyone come across this before and have any ideas?

Many thanks

Harry Boughen replied to Pete Bradshaw on 21-May-13 07:02 PM
Hello Pete,

myShape.OnAction = "'"&ActiveWorkbook.Name & "'!Sheet1.ShowRows"

And if that fails try leaving out the Sheet1.

Pete Bradshaw replied to Harry Boughen on 03-Jun-13 09:38 AM
Hi Harry,

I know it's been a couple of weeks since you replied (it's been a busy few weeks!), but you've hit the nail on the head with this!

Many thanks for your help, you've saved me a few more grey hairs!