Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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"? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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"? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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"? . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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"? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change the macro called by an add in from older excel? | Excel Discussion (Misc queries) | |||
SIZE Window, Tile, Worksheet, Workbook, Whatever it's called. | New Users to Excel | |||
Returning an Array from a called function within a macro | New Users to Excel | |||
which FormField called a macro? | Excel Programming | |||
Called macro runs twice in excel 2002 | Excel Programming |