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

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" wrote in
message ...

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

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

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" wrote
in message ...

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

Bob Phillips Wrote:
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.


Bob Phillips Wrote:
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


--
mliungman
------------------------------------------------------------------------
mliungman's Profile: http://www.excelforum.com/member.php...o&userid=30397
View this thread: http://www.excelforum.com/showthread...hreadid=500670

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")


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=500670

Well that could have been one way of doing it.

However, since the signature has to be secured, random and at the sam
time traceable, the "Create signature" macro is password protected an
cannot be accessed by our staff. Only the small macro (also passwor
protected) calling the "Create signature" macro has access to th
"Create signature" macro.

My staff should only have to press a button, linked to the small macr
that opens and runs the "Create signature" macro, that would the
insert a newly created signature in the ActiveCell of the workboo
waiting for a signature.

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

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

Marti

--
mliungma
-----------------------------------------------------------------------
mliungman's Profile: http://www.excelforum.com/member.php...fo&userid=3039
View this thread: http://www.excelforum.com/showthread.php?threadid=50067

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" wrote
in message ...

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

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

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" wrote
in message ...

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