Macro in another workbook 2

[terrygtx]

I wondering if there is a way to have a button in a macro free workbook call a macro in another workbook AND for the macro to know the filename of the workbook with the button.

Basically, I need to have a macro free workbook that is shared and edited by different people. I don't want them to have to answer the security questions that come with a macro enable workbook. But at the same time, I would like to invoke macro functions on the workbook when I am editing it.

I know that I can assign a macro from a different book to a button residing in this book. But, I can't think of anyway to pass the name of the 'calling' workbook to the function. Can anybody think of a way to do this? I may simply have to present a form that list ALL open workbooks and then choose which one to work in.

Thanks in advance for any tips,

t

 

[SkipVought]

"...a button in a macro free workbook call a macro in another workbook..."

If macros are disabled, how could a button in your "macro free workbook" call ANYTHING???



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TonyJollans]

So you'd like to be able to run macros without the user having to respond to the security prompt? You and every virus writer under the sun.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[combo]

What you expect is close to what you can get with excel add-in, distributed to selected users. When installed, it will stay invisible for the user.
But instead of using macro buttons linked to a macro in the add-in, it is better to build custom menu and test for active workbook.
ActiveWorkbook returns the workbook that has currently active window, you can use its Name, FullName or Path properties for details.


combo

 

[terrygtx]

>If macros are disabled, how could a button in your "macro >free workbook" call ANYTHING???

You can link a button in a macro free workbook (.xls in 2007) to a macro in macro enabled workbook (.xlsm in 2007). Right-click on the button and choose 'assign macro'. The security stuff then happens with the .xlsm workbook not with the call from the .xls workbook.


>So you'd like to be able to run macros without the user having to respond to the security prompt? You and every virus writer under the sun.

NO! That is not it at all! I want the other users to open a non-macro enabled workbook to make their edits. But, I also want to be able to use macros when *I* edit the same workbook. When I click the button that links to the macro in the other workbook, I will have to answer the security prompt which will come from opening the macro enabled workbook. If another user clicked the same button, they would actually get an error message because they don't have the workbook with the macros on their computer. If for some reason they did, they would also see the prompt and would be able to choose for themselves.

 

[terrygtx]

I guess I was hoping there may be some property that tells what the last active workbook was. Sort of like HTTP_REFERER does for web pages.

t

 

[TonyJollans]

A-ha! I understand now. The easiest way to get the name of the workbook is - as combo says - ActiveWorkbook.name as the button can only have been pressed in the active workbook.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[ck1999]

If you want to have macros you can use but others cannot then create an addin and place the macros there. Place this addin only on your computer in the xlstart folder that way you can access it from your computer but others cannot. This is what I do and it works fine. Then create a ribbon with buttons for your macros.

ck1999

 

[terrygtx]

>ActiveWorkbook returns the workbook

>A-ha! I understand now. The easiest way to get the name of >the workbook is - as combo says - ActiveWorkbook.name as the >button can only have been pressed in the active workbook.

You are both right - that did work. I must have screwed something up, because I tested it yesterday and I kept getting the name of the workbook with the macro. I thought it must activate the workbook before running any of the macros.

Thanks a ton!

 

[terrygtx]

>If you want to have macros you can use but others cannot then create an addin and place the macros there. Place this addin only on your computer in the xlstart folder that way you can access it from your computer but others cannot.


This would work, but it would force this workbook to open even when I am working on other non-related projects. But thank you.

t

 

[TonyJollans]

> I must have screwed something up, because I tested it yesterday and I kept getting the name of the workbook with the macro.

Most likely you used ThisWorkbook instead of ActiveWorkbook. It's something we all do from time to time and, once you've coded it, you just can't see the error staring you in the face!

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website