Calling a public Macro from a Private Macro

[thewizz]

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!

 

[Otto Moehrbach]

ActiveWorkbook is the active workbook. ThisWorkbook is the workbook that
holds the macro that is running. Does that help? Otto

 

[Jim Thomlinson]

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.

 

[Don Guillett]

try putting in a regular module

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$12" Then runme
End Sub

 

[thewizz]

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!

 

[thewizz]

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.

 

[jerome drean]

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

ActiveWorkbook is the active workbook. ThisWorkbook is the workbook that
holds the macro that is running. Does that help? Otto