Using a Macro from another document

[residnt]

Hello All,

I am trying to figure out a problem that I have created for myself. I have a document that has a couple of macros the run as soon as the workbook is closed. This part is fine and it all works the problem is that now that several people are using this document any updates that I need to make to this code I have to manually edit each copy of the document. So what I want to do is have the macro act like a tool. I know I can use an add-in but then I would need to get everyone to install it.

Is there a way to just call a macro from a closed document.

Thanks
residnt

 

[residnt]

Ok so I will now be using a hidden sheet to call the functions. But how do I send a variable to the hidden sheet. Ok let me explain better.

The workbook that will be calling the hidden sheet has an workbook.open function. In there I grab the name of the workbook as I need this to update the code from the hidden sheet. However how do I pass the value from this variable to a different workbook.

Hopefully that is clear.

Thanks
residnt

 

[Smitty]

Check out "Understanding Scope and Lifetime of Variables" (or something to that effect) in the VBA helpfile.

You can also check out Chip Pearson's site www.cpearson.com for his section on Programming to the VBE. If you use that technique you can actually send a workbook that when opened will update a wb.

You might also want to try posting your code.

HTH,

Smitty

 

[residnt]

Thanks for the links it will take me a bit to go through them. Here is the code that I have

Static Sub Workbook_Open()

pth = ThisWorkbook.Name
MsgBox pth
    On Error Resume Next
    x = Workbooks("publish_numbers.xls").Name
    If Not Err = 0 Then
        On Error GoTo 0
        Workbooks.Open Filename:= _
            "R:\PAD\finishing_team\BudgetNumbers\publish_numbers.xls"
    End If
    On Error GoTo 0

    Application.Run "publish_numbers.xls!os_numbers"

end sub

So what I need to do is pass the value of pth to os_numbers

Thanks
residnt

EDIT: added code tags - Smitty

 

[residnt]

Ok I have found the solution

I needed to add in the argument in the application.run (macro), pth

and in the sub where I wanted it i needed to add this

sub os_numbers(pth)

Thanks again for your help

residnt