Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to force macros refer to ActiveWorkbook
Subject: How to force macros refer to ActiveWorkbook
I am on Excel 2007. I am struggling to write macros which are stored in a central place. I have created macros in a file SCmacros.xlsm, I open this file. Macro files refer to various worksheets. These worksheets do not exist in macros file; instead they exist in SCdata.xls file discussed below, I now have another excel file SCdata.xls which calls macros in SCmacros.xlsm, I open in the SCdata.xls file and call a macro SCmacros.xlsm!test Problem is test macro seems to finding worksheets in SCmacros.xlsm file instead of in ActiveWorkbook Scdata.xls. How can I make simple changes such as setting up some events in SCmacros.xlsm macros refer to Worksheets in ActiveWorkbook Scdata.xls. I did try to put : ThisWorkbook = ActiveWorkbook n my test macro but it did not work!! I do not want to make SCmacros.xlsm a plug in because that creates another set of problems. Thanks a lot, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to force macros refer to ActiveWorkbook
On Friday, September 21, 2012 9:05:18 PM UTC-4, (unknown) wrote:
Subject: How to force macros refer to ActiveWorkbook I am on Excel 2007. I am struggling to write macros which are stored in a central place. I have created macros in a file SCmacros.xlsm, I open this file. Macro files refer to various worksheets. These worksheets do not exist in macros file; instead they exist in SCdata.xls file discussed below, I now have another excel file SCdata.xls which calls macros in SCmacros.xlsm, I open in the SCdata.xls file and call a macro SCmacros.xlsm!test Problem is test macro seems to finding worksheets in SCmacros.xlsm file instead of in ActiveWorkbook Scdata.xls. How can I make simple changes such as setting up some events in SCmacros.xlsm macros refer to Worksheets in ActiveWorkbook Scdata.xls. I did try to put : ThisWorkbook = ActiveWorkbook n my test macro but it did not work!! I do not want to make SCmacros.xlsm a plug in because that creates another set of problems. Thanks a lot, I have even tried to all my marcos in personal.xls file, but when I rn macro from Scdata.xls file (View macros, select personal.xls!test macr and run), test macro errors that worksheel in Scdata.xls do not exist; then what is poinyt of putting macros in personal.xls. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to force macros refer to ActiveWorkbook
Hello,
Assuming that your macros will be called from whatever workbook is active, you may consider adding this to each macro: Dim wbThisWB As Workbook Set wbThisWB = ActiveWorkbook Then, when you reference sheets in your macro (for example "MySheet"), use the "wbThisWB" object. For example: wbThisWB.Sheets("Sheet3").Activate Best of luck, Ben |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Force to enable macros | Excel Programming | |||
ActiveWorkbook pointer for Macros from PERSONAL.XLS | Excel Programming | |||
ActiveWorkbook pointer for Macros from PERSONAL.XLS | Excel Programming | |||
Force Enable Macros | Excel Programming | |||
Force Enable Macros | Excel Programming |