Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 16
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 16
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 173
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Force to enable macros Denys[_2_] Excel Programming 4 July 6th 06 02:24 PM
ActiveWorkbook pointer for Macros from PERSONAL.XLS Robert Excel Programming 0 July 25th 05 10:03 AM
ActiveWorkbook pointer for Macros from PERSONAL.XLS Robert Excel Programming 3 July 19th 05 07:04 AM
Force Enable Macros pgjoshi[_2_] Excel Programming 3 March 6th 04 01:33 PM
Force Enable Macros Ed[_9_] Excel Programming 0 September 24th 03 07:59 PM


All times are GMT +1. The time now is 08:34 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"