Passing variables between workbooks

[mliungman]

Hi!

I'm using a "Create signature" macro in a file on our server, to create individual signatures in various excel workbooks.

Let's say I'm in a workbook that needs a signature. I want to select a cell where the signature is to be inserted, and then call the macro in the "Create signature" workbook. My problem is that I don't know how to make the "Create signature" macro know which workbook is waiting for a signature. Creating a global variable won't work, and I'm not familiar with passing variables between workbooks.

Please advice!

Martin

[Bob Phillips]

Why won't a global variable work, or even a private one come to that? If you
open your workbook to have a signature added from within your master book,
you don't need to pas anything to it.

Set oWB = Workbooks.Open("C:\myFile.xls")
oWB.Worksheets(1).Range"A1").value = "signature"
oWB.Save
oWB.Close

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"mliungman" <[email protected]> wrote in
message news:[email protected]...
>
> Hi!
>
> I'm using a "Create signature" macro in a file on our server, to create
> individual signatures in various excel workbooks.
>
> Let's say I'm in a workbook that needs a signature. I want to select a
> cell where the signature is to be inserted, and then call the macro in
> the "Create signature" workbook. My problem is that I don't know how to
> make the "Create signature" macro know which workbook is waiting for a
> signature. Creating a global variable won't work, and I'm not familiar
> with passing variables between workbooks.
>
> Please advice!
>
> Martin
>
>
> --
> mliungman
> ------------------------------------------------------------------------
> mliungman's Profile:
http://www.excelforum.com/member.php...o&userid=30397
> View this thread: http://www.excelforum.com/showthread...hreadid=500670
>

[mliungman]

Hi and thanks for the reply!

I might be all wrong here, but do you mean I should RETRIEVE a signature from the "Create signature" macro, instead of having the macro DELIVER a signature to the waiting workbook? If so, how? Because at the moment there is no macro whatsoever in the receiving workbook, only in the "Create signature" workbook. I don't know which files might need a signature, and would much prefer to have the signature-creating macro in only one workbook on the server, and no macros in the other workbooks.

Observe that I'm working with one "Create signature" macro in one workbook, and another workbook (with any name) into which the signature created by the "Create signature" workbook is to be inserted. I call on the "Create signature" macro from the workbook (with any name).

Sorry if I'm completely misunderstanding something.

Martin

[Bob Phillips]

No, I mean that the macro would be in a master and it would add the
signature. Though, if you don't know which workbooks will get a signature, I
have no idea how the macro will.

In the scenario you describe, the 'any name workbook' will need code to call
upon 'Create Signature' macro. I was suggesting that you had some code in
the master that opens the workbook, and once you establish that that
workbook needs a signature (I do not know how) that same code adds it.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"mliungman" <[email protected]> wrote
in message news:[email protected]...
>
> Hi and thanks for the reply!
>
> I might be all wrong here, but do you mean I should RETRIEVE a
> signature from the "Create signature" macro, instead of having the
> macro DELIVER a signature to the waiting workbook? If so, how? Because
> at the moment there is no macro whatsoever in the receiving workbook,
> only in the "Create signature" workbook. I don't know which files might
> need a signature, and would much prefer to have the signature-creating
> macro in only one workbook on the server, and no macros in the other
> workbooks.
>
> Observe that I'm working with one "Create signature" macro in one
> workbook, and another workbook (with any name) into which the signature
> created by the "Create signature" workbook is to be inserted. I call on
> the "Create signature" macro from the workbook (with any name).
>
> Sorry if I'm completely misunderstanding something.
>
> Martin
>
>
> --
> mliungman
> ------------------------------------------------------------------------
> mliungman's Profile:
http://www.excelforum.com/member.php...o&userid=30397
> View this thread: http://www.excelforum.com/showthread...hreadid=500670
>

[mliungman]

No, I mean that the macro would be in a master and it would add the
signature. Though, if you don't know which workbooks will get a signature, I
have no idea how the macro will.

The workbook that needs a signature will be open, and will have a specific, known, name. What I meant was that we have many template workbooks that will eventually need signatures, after they have been individually named.

In the scenario you describe, the 'any name workbook' will need code to call
upon 'Create Signature' macro. I was suggesting that you had some code in
the master that opens the workbook, and once you establish that that
workbook needs a signature (I do not know how) that same code adds it.

The open workbook could call on the "Create signature" macro in a workbook on the server as you describe. What I don't understand is how to make the macro return to the original workbook to insert the created signature. To do this, the "Create signature" macro needs the file name of the workbook that called for it. I could solve this by pasting the file name into a cell in the "Create signature" workbook, and then have the "Create signature" read that cell to get the file name. But I would much rather pass the name as some kind of variable, thus my original question: how do I pass a variable from one workbook to another?

I think i just need some help with syntax. A global variable just works in one project, right? Can I create a variable that can move between projects?

Thanks again

Martin

[tony h]

In the create signature spreadsheet you have a macro


Sub CreateSignature()

ActiveCell = "myvalue"
End Sub

then open the workbook that needs the signature, slect the cell where the signature is needed and select tools:macros run Create Signature.

If the signature is to go in a standard place then you could use

activeworkbook.worksheets("sheet name").range("A7")

[mliungman]

Well that could have been one way of doing it.

However, I don't want my staff to have to search the server for the "Create signature" spreadsheet. I want them to have a button or shortcut that is linked to a macro activating the spreadsheet. It has to be easy for them to use the application.

I could copy the "Create signature" macro to all 15 workstations (or into all excel templates), and have the macro running from the workbook that needs a signature. If I wanted to modify the "Create signature" macro I would have to copy the module to all workstations (or templates) again.

But I REALLY want to have ONE copy of the macro in ONE workbook on our server. That way all changes will have immediate effect on all workstations. But that requires passing the name of the open workbook to the "Create signature" macro on the server. I think...

Martin

[Bob Phillips]

Put the code in an add-in on the server, and get that add-in installed on
each desktop.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"mliungman" <[email protected]> wrote
in message news:[email protected]...
>
> Well that could have been one way of doing it.
>
> However, since the signature has to be secured, random and at the same
> time traceable, the "Create signature" macro is password protected and
> cannot be accessed by our staff. Only the small macro (also password
> protected) calling the "Create signature" macro has access to the
> "Create signature" macro.
>
> My staff should only have to press a button, linked to the small macro
> that opens and runs the "Create signature" macro, that would then
> insert a newly created signature in the ActiveCell of the workbook
> waiting for a signature.
>
> I could copy the "Create signature" macro to all 15 workstations (or
> into all excel templates), and have the macro running from the workbook
> that needs a signature. If I wanted to modify the "Create signature"
> macro I would have to copy the module to all workstations (or
> templates) again.
>
> But I REALLY want to have ONE copy of the macro in ONE workbook on our
> server. That way all changes will have immediate effect on all
> workstations. But that requires passing the name of the open workbook
> to the "Create signature" macro on the server. I think...
>
> Martin
>
>
> --
> mliungman
> ------------------------------------------------------------------------
> mliungman's Profile:
http://www.excelforum.com/member.php...o&userid=30397
> View this thread: http://www.excelforum.com/showthread...hreadid=500670
>

[mliungman]

Great Bob, you're a wizard!

And I have to learn how to ask questions clearer...

Thanks!

[Bob Phillips]

Often it is difficult to stand-back and pick out the salient facts,
especially when we have been grappling with it for hours or days <G>

Bob

"mliungman" <[email protected]> wrote
in message news:[email protected]...
>
> Great Bob, you're a wizard!
>
> And I have to learn how to ask questions clearer...
>
> Thanks!
>
>
> --
> mliungman
> ------------------------------------------------------------------------
> mliungman's Profile:
http://www.excelforum.com/member.php...o&userid=30397
> View this thread: http://www.excelforum.com/showthread...hreadid=500670
>