Running an Excel macro from another workbook

[Pavel Kotrc]

Got a problem... is it possible to run a macro in some workbook from another workbook (both of them are open)? I have created a custom menu item, but it applies only to the workbook by which it was created - so when I close that WB I need to "recreate" the menu item from workbook #2 in case it is open.

Any ideas? Thanx in advance.

[VB_Learner]

Hi,
Where do you save your macro? there 3 places where you can save you macros and it works differently.

find the details bellow

There are three possible workbooks where you can store your macros:


This Workbook - This is the default location and is often the best place if you are relatively new to macros. A macro that has been saved into a specific workbook is only available when that particular workbook is open. The currently active workbook is also referred to as the current workbook or active workbook.


New Workbook - A completely new workbook will be opened for you automatically. This can be useful for quickly examining the generated code but remember that the workbook you store the macro in must be open in order for the macro to be run.


Personal Macro Workbook - Storing your macros here will mean that they are available every time Excel is open and are not reliant on any one particular workbook.

hope, it helps in solving the problem....

Regards

Deepak

[Pavel Kotrc]

Thanks for your reply. My macro is stored in a module inside the workbook (as a "sub") and it is called when:
1) the workbook is being opened
2) the custom menu item is not yet created

In case the menu item is already created, it is not called. What I want to do is to "give control" of the menu item to WB #2 when WB #1 is being closed.

[VB_Learner]

Hi,
Follow the paths to create your macro, which will run for each WB you open.

Select (Tools > Macro > Record New Macro) to display the Record Macro dialog box.


Alternatively you could press the "Record Macro" button on the Visual Basic toolbar.


(Tools > Macro > Record New Macro) dialog box


The name of your macro will automatically default to "Macro1", "Macro2", etc depending on the number of macros that have been recorded in that specific workbook.


Macro names must begin with a letter and cannot resemble cell addresses (i.e. A1, B5, BT100 etc).


Macro names cannot include spaces although the underscore character can be used (e.g. "macroname_2"). You can also include numbers in your macro names.


It is sometimes easier to accept the default name and then change the name of the procedure later.


The shortcut key and description are optional so do not worry about these at this point, however it is very important to remember where your macro will be stored.


There are three possible workbooks where you can store your macros:


This Workbook - This is the default location and is often the best place if you are relatively new to macros. A macro that has been saved into a specific workbook is only available when that particular workbook is open. The currently active workbook is also referred to as the current workbook or active workbook.


New Workbook - A completely new workbook will be opened for you automatically. This can be useful for quickly examining the generated code but remember that the workbook you store the macro in must be open in order for the macro to be run.


Personal Macro Workbook - Storing your macros here will mean that they are available every time Excel is open and are not reliant on any one particular workbook.


Once you press OK a small floating toolbar will appear. This is the Stop Recording toolbar and indicates that any keystrokes you make will be recorded.


The status bar also displays the message "Recording".


You can now perform the keystrokes that you would like to be automated.


A new code module is often inserted into the active workbook automatically to contain the generated VBA code.


Stop Recording toolbar


Excel will default to recording all the cell references as Absolute. These are recorded in A1 notation.


This means that any cell references that are selected are explicitly referred to and you will always get exactly the same result when the macro is played back.


Absolute references are useful when you want to perform the same action in an identical place every time.


When the button is pressed, relative references are used and when it is not pressed absolute references are used.



In some cases you may want the recorded macro to work with cell references that are Relative to a particular cell. These are recorded in R1C1 notation.


For example you might want to enter some data into the active cell and also enter some data into the cell directly below.


Relative references are useful when you want to perform an action anywhere on a worksheet.


You can change to relative recording by pressing the button on the right hand side of the Stop Recording toolbar. This button is a toggle.


You can change between absolute and relative references at any time while you are recording your macro.


Be aware that there is no indication given other than the button being pushed in, as to which mode you are currently in. The first recorded macro in a workbook will always start by using absolute references.


If you stop recording when you have been using relative references the next recorded macro will start by using relative references.


Using relative references will generate code that always refers to the cell currently selected before you start recording and then uses the "Offset" method to obtain the relative cell address.


Regards

Deepak

[Pavel Kotrc]

Hey, I'm afraid you're a little off-topic. I don't want to record a macro. I have created some macros in the VBA and stored them in a module of the specific workbook. Now what I want is to call the macro from another open workbook... if it is possible.