Referring back to calling spreadsheet from presonal.xlsb

[ijourneaux]

I have a bunch of macros that I keep in a personal.xlsb spreadsheet so they are avaialble to all of the workbook I use.

When in a macro in personal.xlsb, I refer back to the calling spreadsheet using activeworkbook.

That works as long as I only have 1 spreadsheet up but if I am using excel and have several workbooks open, the macros can break if the wrong workbook is active.

Is there a way from Personal.xlsb to refer back to the specific spreadsheet that called the macro?

Ian

 

[Yongle]

What I do is to pass an identifying variable from the calling macro to the one being called

Away from PC, so untested and from memory, something along these lines ...

In workbook where you are running the macro

Private Sub CommandButton1_Click()
    Call MyMacro(ThisWorkbook)
End Sub

In Personal workbook

Sub MyMacro(wb As Workbook)
    MsgBox "WhoCalledThis = " & wb.Name
End Sub

 

[ijourneaux]

Yes that works as it should. Thanks for the tip!