Yet another question!!!

I am trying to use the Worksheet_change Event to call a macro that I have
placed in the "ThisWorkbook" folder using Application.run. The problem I am
having is that I do not want to use the specific name of the workbook in the
statement because it can change since I am making a "Template". Any
suggestions how I can call the Active workbook instead?!?! I have tried
several itterations and nothing seems to work. I keep getting application
failure. I assume it cannot find the macro....

Example:

Worksheet name - myworksheet
macro name - runme

Application.run "'myworksheet.xls'!ThisWorkbook.runme"

but replace "myworksheet.xls" with a generic statement

Thank you!
--
I am not where I intended to go, but I think I am where I am supposed to be!

ActiveWorkbook is the active workbook. ThisWorkbook is the workbook that
holds the macro that is running. Does that help? Otto
"thewizz" <(E-Mail Removed)> wrote in message
news:7FABFA63-ED9C-463C-9315-(E-Mail Removed)...
> Yet another question!!!
>
> I am trying to use the Worksheet_change Event to call a macro that I have
> placed in the "ThisWorkbook" folder using Application.run. The problem I
> am
> having is that I do not want to use the specific name of the workbook in
> the
> statement because it can change since I am making a "Template". Any
> suggestions how I can call the Active workbook instead?!?! I have tried
> several itterations and nothing seems to work. I keep getting application
> failure. I assume it cannot find the macro....
>
> Example:
>
> Worksheet name - myworksheet
> macro name - runme
>
> Application.run "'myworksheet.xls'!ThisWorkbook.runme"
>
> but replace "myworksheet.xls" with a generic statement
>
> Thank you!
> --
> I am not where I intended to go, but I think I am where I am supposed to
> be!

In order to call procedures in ThisWorkbook from outside of the ThisWorkbook
module you need to preface the call with ThisWorkbook. So for example in
Module 1 to call a procedure in ThisWorkbook you need to use something like
this...

Call ThisWorkbook.RunMe

I am unclear why you would be worried about which is the active workbook and
using Application.Run? Are you trying to run a procedure located in one
workbook on a different workbook? So are you wanting to run
Book1.ThisWorkbook.RunMe on as sheet in book2? If so then that is a different
beast all together.
--
HTH...

Jim Thomlinson


"thewizz" wrote:

> Yet another question!!!
>
> I am trying to use the Worksheet_change Event to call a macro that I have
> placed in the "ThisWorkbook" folder using Application.run. The problem I am
> having is that I do not want to use the specific name of the workbook in the
> statement because it can change since I am making a "Template". Any
> suggestions how I can call the Active workbook instead?!?! I have tried
> several itterations and nothing seems to work. I keep getting application
> failure. I assume it cannot find the macro....
>
> Example:
>
> Worksheet name - myworksheet
> macro name - runme
>
> Application.run "'myworksheet.xls'!ThisWorkbook.runme"
>
> but replace "myworksheet.xls" with a generic statement
>
> Thank you!
> --
> I am not where I intended to go, but I think I am where I am supposed to be!

try putting in a regular module

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$12" Then runme
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"thewizz" <(E-Mail Removed)> wrote in message
news:7FABFA63-ED9C-463C-9315-(E-Mail Removed)...
> Yet another question!!!
>
> I am trying to use the Worksheet_change Event to call a macro that I have
> placed in the "ThisWorkbook" folder using Application.run. The problem I
> am
> having is that I do not want to use the specific name of the workbook in
> the
> statement because it can change since I am making a "Template". Any
> suggestions how I can call the Active workbook instead?!?! I have tried
> several itterations and nothing seems to work. I keep getting application
> failure. I assume it cannot find the macro....
>
> Example:
>
> Worksheet name - myworksheet
> macro name - runme
>
> Application.run "'myworksheet.xls'!ThisWorkbook.runme"
>
> but replace "myworksheet.xls" with a generic statement
>
> Thank you!
> --
> I am not where I intended to go, but I think I am where I am supposed to
> be!

Maybe I'm thinking incorrect...

I am using the Worksheet_change event to watch a cell in 1 of 10 sheets in
my workbook. If the cell changes value (someone types a new value) I want to
run the code "runme" located in the "ThisWorkbook" folder. I thought
(probably incorrectly) that I could not use the "Call" command from private
macro within a specific worksheet folder to call a macro in a different
folder. I have tried:

Call ThisWorkbook.runme

but it comes up with "Run-time error '1004'" "Application-defined or
object-defined error"

Thank you!
--
I am not where I intended to go, but I think I am where I am supposed to be!


"Jim Thomlinson" wrote:

> In order to call procedures in ThisWorkbook from outside of the ThisWorkbook
> module you need to preface the call with ThisWorkbook. So for example in
> Module 1 to call a procedure in ThisWorkbook you need to use something like
> this...
>
> Call ThisWorkbook.RunMe
>
> I am unclear why you would be worried about which is the active workbook and
> using Application.Run? Are you trying to run a procedure located in one
> workbook on a different workbook? So are you wanting to run
> Book1.ThisWorkbook.RunMe on as sheet in book2? If so then that is a different
> beast all together.
> --
> HTH...
>
> Jim Thomlinson
>
>
> "thewizz" wrote:
>
> > Yet another question!!!
> >
> > I am trying to use the Worksheet_change Event to call a macro that I have
> > placed in the "ThisWorkbook" folder using Application.run. The problem I am
> > having is that I do not want to use the specific name of the workbook in the
> > statement because it can change since I am making a "Template". Any
> > suggestions how I can call the Active workbook instead?!?! I have tried
> > several itterations and nothing seems to work. I keep getting application
> > failure. I assume it cannot find the macro....
> >
> > Example:
> >
> > Worksheet name - myworksheet
> > macro name - runme
> >
> > Application.run "'myworksheet.xls'!ThisWorkbook.runme"
> >
> > but replace "myworksheet.xls" with a generic statement
> >
> > Thank you!
> > --
> > I am not where I intended to go, but I think I am where I am supposed to be!

Jim,

Thank you for your help! It made me think which led me to the answer! It
is working now, but for some reason I had to leave the word "Call" out to
make it work.

What I have is:

ThisWorkbook.Runme

And it run fine.
--
I am not where I intended to go, but I think I am where I am supposed to be!


"Jim Thomlinson" wrote:

> In order to call procedures in ThisWorkbook from outside of the ThisWorkbook
> module you need to preface the call with ThisWorkbook. So for example in
> Module 1 to call a procedure in ThisWorkbook you need to use something like
> this...
>
> Call ThisWorkbook.RunMe
>
> I am unclear why you would be worried about which is the active workbook and
> using Application.Run? Are you trying to run a procedure located in one
> workbook on a different workbook? So are you wanting to run
> Book1.ThisWorkbook.RunMe on as sheet in book2? If so then that is a different
> beast all together.
> --
> HTH...
>
> Jim Thomlinson
>
>
> "thewizz" wrote:
>
> > Yet another question!!!
> >
> > I am trying to use the Worksheet_change Event to call a macro that I have
> > placed in the "ThisWorkbook" folder using Application.run. The problem I am
> > having is that I do not want to use the specific name of the workbook in the
> > statement because it can change since I am making a "Template". Any
> > suggestions how I can call the Active workbook instead?!?! I have tried
> > several itterations and nothing seems to work. I keep getting application
> > failure. I assume it cannot find the macro....
> >
> > Example:
> >
> > Worksheet name - myworksheet
> > macro name - runme
> >
> > Application.run "'myworksheet.xls'!ThisWorkbook.runme"
> >
> > but replace "myworksheet.xls" with a generic statement
> >
> > Thank you!
> > --
> > I am not where I intended to go, but I think I am where I am supposed to be!

hi
alternatively create a *.xla put your sub(byval sheetname as string) there
and call it using the worksheet you want it to perform on
hope it helps
good luck


"Otto Moehrbach" wrote:

> ActiveWorkbook is the active workbook. ThisWorkbook is the workbook that
> holds the macro that is running. Does that help? Otto
> "thewizz" <(E-Mail Removed)> wrote in message
> news:7FABFA63-ED9C-463C-9315-(E-Mail Removed)...
> > Yet another question!!!
> >
> > I am trying to use the Worksheet_change Event to call a macro that I have
> > placed in the "ThisWorkbook" folder using Application.run. The problem I
> > am
> > having is that I do not want to use the specific name of the workbook in
> > the
> > statement because it can change since I am making a "Template". Any
> > suggestions how I can call the Active workbook instead?!?! I have tried
> > several itterations and nothing seems to work. I keep getting application
> > failure. I assume it cannot find the macro....
> >
> > Example:
> >
> > Worksheet name - myworksheet
> > macro name - runme
> >
> > Application.run "'myworksheet.xls'!ThisWorkbook.runme"
> >
> > but replace "myworksheet.xls" with a generic statement
> >
> > Thank you!
> > --
> > I am not where I intended to go, but I think I am where I am supposed to
> > be!
>
>
>