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

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"
    OR
myShape.OnAction = ActiveWorkbook.Path & "!Sheet1.ShowRows"

Has anyone come across this before and have any ideas?

Many thanks

Pete

Hello Pete,

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

And if that fails try leaving out the Sheet1.

Regards
Harry

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!

Cheers

Pete