Keeping name of worksheet who called macro
 

I have a pair of workbooks. One is created by a coworker, mine provides a
number of utilities back to the coworker's workbook.

Coworker's workbook has a name "VLAPPI". My workbook is titled "Scenarios".

My coworker has graciously included code in ThisWorkbook to call my workbook
upon startup or activation. My workbook addes a menu of my utilities to the
VLAPPI workbook.

In one of the utilities I provide , a new file is created, activated,
renamed, and saved. After saving the new file I need to get back to the
VLAPPI workbook.

The problem is the file may not necessarilty be called VLAPPI. It coould
have some other name. I don't know how to active that workbook if it is
under a different name.

Right now I'm using Windows("VLAPPI.xls").Activate

But if the name is different this blows up.

How can I set a public variable for the workbook that the menu selection was
made from to run my utility and that will work regardless of the name of my
coworker's workbook? So if the work book is named "VLAPPX" I can reactive
the "calling" workbook "VLAPPX" when my utility has run its course.

Would it help if I told him he can change the name but the first two letters
had to be "VL"?

Keeping name of worksheet who called macro
 

The workbook activating your workbook would be the one to set any variables.

Try this:

Dim TempBook As Workbook
For Each TempBook In Workbooks
'Check for something here that always exists and is unique
If TempBook.BuiltinDocumentProperties("Author").Value = '"coworker's name"
Then
tempbook.activate
MsgBox ("Here's the workbook: " & TempBook.Name)
Exit For
End If
Next

Ken V.

"Dkline" wrote in message
...
I have a pair of workbooks. One is created by a coworker, mine provides a
number of utilities back to the coworker's workbook.

Coworker's workbook has a name "VLAPPI". My workbook is titled
"Scenarios".

My coworker has graciously included code in ThisWorkbook to call my
workbook
upon startup or activation. My workbook addes a menu of my utilities to
the
VLAPPI workbook.

In one of the utilities I provide , a new file is created, activated,
renamed, and saved. After saving the new file I need to get back to the
VLAPPI workbook.

The problem is the file may not necessarilty be called VLAPPI. It coould
have some other name. I don't know how to active that workbook if it is
under a different name.

Right now I'm using Windows("VLAPPI.xls").Activate

But if the name is different this blows up.

How can I set a public variable for the workbook that the menu selection
was
made from to run my utility and that will work regardless of the name of
my
coworker's workbook? So if the work book is named "VLAPPX" I can reactive
the "calling" workbook "VLAPPX" when my utility has run its course.

Would it help if I told him he can change the name but the first two
letters
had to be "VL"?

Keeping name of worksheet who called macro
 

How are your utilities executed?

I understand that your coworker uses the Workbook_Open
event to call your workbook. Does he open your workbook
and then execute a procedure in it?

Have him do something like this:

Private Sub Workbook_Open()
dim util as Workbook
set util = Workbooks.Open("C:\Ultility.xls")
Call util.CopyFile(ThisWorkbook.Name)
End Sub

Then have your "CopyFile" subroutine use the Workbook name
argument.

HTH.
-Brad
-----Original Message-----
I have a pair of workbooks. One is created by a coworker,
mine provides a
number of utilities back to the coworker's workbook.

Coworker's workbook has a name "VLAPPI". My workbook is
titled "Scenarios".

My coworker has graciously included code in ThisWorkbook
to call my workbook
upon startup or activation. My workbook addes a menu of
my utilities to the
VLAPPI workbook.

In one of the utilities I provide , a new file is
created, activated,
renamed, and saved. After saving the new file I need to
get back to the
VLAPPI workbook.

The problem is the file may not necessarilty be called
VLAPPI. It coould
have some other name. I don't know how to active that
workbook if it is
under a different name.

Right now I'm using Windows("VLAPPI.xls").Activate

But if the name is different this blows up.

How can I set a public variable for the workbook that the
menu selection was
made from to run my utility and that will work regardless
of the name of my
coworker's workbook? So if the work book is
named "VLAPPX" I can reactive
the "calling" workbook "VLAPPX" when my utility has run
its course.

Would it help if I told him he can change the name but
the first two letters
had to be "VL"?


.

Keeping name of worksheet who called macro
 

In the macro assigned to your menu item

Sub MyMenuButton1_click()
Dim wkbk = Activeworkbook
- do all your work
wkbk.Activate
End Sub

--
Regards,
Tom Ogilvy

"Dkline" wrote in message
...
I have a pair of workbooks. One is created by a coworker, mine provides a
number of utilities back to the coworker's workbook.

Coworker's workbook has a name "VLAPPI". My workbook is titled
"Scenarios".

My coworker has graciously included code in ThisWorkbook to call my
workbook
upon startup or activation. My workbook addes a menu of my utilities to
the
VLAPPI workbook.

In one of the utilities I provide , a new file is created, activated,
renamed, and saved. After saving the new file I need to get back to the
VLAPPI workbook.

The problem is the file may not necessarilty be called VLAPPI. It coould
have some other name. I don't know how to active that workbook if it is
under a different name.

Right now I'm using Windows("VLAPPI.xls").Activate

But if the name is different this blows up.

How can I set a public variable for the workbook that the menu selection
was
made from to run my utility and that will work regardless of the name of
my
coworker's workbook? So if the work book is named "VLAPPX" I can reactive
the "calling" workbook "VLAPPX" when my utility has run its course.

Would it help if I told him he can change the name but the first two
letters
had to be "VL"?