Use Macro from Source

[Guest]

Hi,

I have Workbooks A and B. Workbook A contains a macro, and Workbook B
contains the data. Usually, I have a command button (in one of the
worksheets) in Workbook B and have it call the macro in Workbook A to execute
the macro.

Is there a way I have a command button created in Workbook A, and when the
button is clicked, the macro will execute for Workbook B (assuming that both
workbooks are open in the same Excel window)? If possible, can someone help
me with the code? The code will be part of macro in Workbook A.

Thanks.

 

[Mike Fogleman]

Put a command button in workbook A and assign the same macro to it that the
workbook B command button uses.

Mike F

 

[Guest]

Mike,

Thanks for your response. The thing is in my macro, every line is
referenced to active workbook. So when I have a button in Workbook B and
when I click on it, the Workbook B is the active workbook.

Would it still work even I reference my macro as active workbook?

Thanks.

 

[Mike Fogleman]

No. But you could add a line that would make Workbook B the active book at
the first of the macro:

Workbooks("Workbook B").Activate

Of course use the real name of your workbook in the above.

Mike F

 

[Bill Renaud]

Normally, I would create a new toolbar (Tools|Customize command), add the
button to it, then attach the toolbar to Workbook A. Hide Workbook A, then
go into the VBA editor and save again (make sure to select Workbook A in
the Project Explorer window first). Now, when Workbook B is active, the
button will automatically perform its actions on Workbook B, assuming that
you set a reference to the active workbook as the first line of code.

Public Sub TryThis()
Dim wbData as Workbook

Set wbData = ActiveWorkbook

'Do your processing here.
End Sub

 

[Guest]

Hi Mike,

Thanks for the reply. Unfortunately, I can not reference the Workbook B in
my code because the name of Workbook B is variable. I will be using my macro
(Workbook A) on 300 workbooks (Workbook B's), and all the 300 workbooks have
different names.

Thanks again.

 

[Guest]

Hi Bill,

Thanks for your response.

First of all, from your instructions, how do I hide the Workbook A?

Secondly, I don't know this would accomplish what I was looking for.
Basically, I will have both Workbooks A and B opened in the same Excel
window. I will have a command button in one of the worksheets in Workbook A,
and when I click on the button, it will execute the macro in Workbook B.
Workbook A has nothing but the button and macro. Workbook B has data, and
the macro will extract the data from Workbook B (after clicking the button in
Workbook A).

As I mentioned to Mike from above, I won't be able to use the code
"Workbooks ("Workbook B").Activate" because the Workbook B name will be
variable.

Anyhow, I will try per your instructions. Thanks.

 

[Bill Renaud]

<<...how do I hide the Workbook A?>>

I use Excel 2000, so I use the Window|Hide command. It may now be in a
different menu for you, if you are using Excel 2007.

<<I will have both Workbooks A and B opened in the same Excel window.>>

This is normal. Workbook A (macros) will be hidden. Workbook B (data) will
be visible.

<<I will have a command button in one of the worksheets in Workbook A...>>

No. If you are using Excel 2007 (which now has ribbons instead of
CommandBars), you will have to figure out how to add a custom toolbar to
the Excel environment, so you can place your button on this toolbar and
attach it to Workbook A.

<<...when I click on the button, it will execute the macro in Workbook B.>>

You mean the macro (in Workbook A) will process the DATA in Workbook B.
There is NO macro in Workbook B!

<<I won't be able to use the code "Workbooks ("Workbook B").Activate"
because the Workbook B name will be variable.>>

True. This is why you set a reference to the ActiveWorkbook almost the very
first thing in your macro, as I suggested in my previous post.

 

[Guest]

Thanks Bill. I have never thought of the hide feature in Excel. I learned
something.