Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Macro from Source
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Macro from Source
Put a command button in workbook A and assign the same macro to it that the
workbook B command button uses. Mike F "AccessHelp" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Macro from Source
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" wrote: Put a command button in workbook A and assign the same macro to it that the workbook B command button uses. Mike F "AccessHelp" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Macro from Source
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 "AccessHelp" wrote in message ... 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" wrote: Put a command button in workbook A and assign the same macro to it that the workbook B command button uses. Mike F "AccessHelp" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Macro from Source
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 -- Regards, Bill Renaud |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Macro from Source
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. "Mike Fogleman" wrote: 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 "AccessHelp" wrote in message ... 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" wrote: Put a command button in workbook A and assign the same macro to it that the workbook B command button uses. Mike F "AccessHelp" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Macro from Source
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" wrote: 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 -- Regards, Bill Renaud |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Macro from Source
<<...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. -- Regards, Bill Renaud |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Macro from Source
Thanks Bill. I have never thought of the hide feature in Excel. I learned
something. "Bill Renaud" wrote: <<...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. -- Regards, Bill Renaud |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Source is Changing | Excel Discussion (Misc queries) | |||
Update Chart Source (w/macro?) | Excel Worksheet Functions | |||
Run a macro when a cell is changed by an outside source | Excel Programming | |||
Macro runs in source , but not when in Personal Macro Workbook | Excel Programming | |||
Changing macro source | Excel Programming |