getting Selection.OnAction to recognize correct workbook

[c1802362]

I have a routine that reduces a large monthly data set, then parses the data into individual workbooks (each month on a separate tab) for a number of different departments. The workbooks are stored online in a common directory for each department to access and update.

In order to improve things, management wants each department to certify they have reviewed and updated the data. I've programmed a form button (as opposed to an activex button) that the user can click when they have completed their data which will send me an e-mail certification.

I've put the e-mail code into the "ThisWorkbook" sheet of each department's workbook. The expectation is that the pointer to the e-mail code will be to the local copy in each department's workbook when the

Please Login or Register  to view this content.

command is generated for each month in each workbook.

The code works as advertised except that the command "

Please Login or Register  to view this content.

= "ThisWorkbook.CertifyEmail" always tries to reference my source code, not the local department copy” (On my machine, my source code opens and runs the e-mail routine without issue - obviously I can't have my users accessing my master file)

Here’s the code that generates the button on each month’s worksheet within each department’s file:

Please Login or Register  to view this content.

Any suggestions how to get the OnAction command to reference the local code in each department's workbook?

Art

[Norie]

Art

Try removing ThisWorkbook.

Please Login or Register  to view this content.

PS Can you edit your post to add code tags?

[c1802362]

Removed the "ThisWorkbook" part of the command - still references my source workbook

Art

[Norie]

Even when people run this code in their own workbooks?

[c1802362]

Yup - each department's workbook skips over the e-mail code located in each workbook and tries to access the origianl code in my source workbook!

[Norie]

So they run this code in a workbookk that has the CertifyEmail sub in it but when they click the button Excel tries to access CertifyEmail in a totally unrelated workbook?

[c1802362]

exactly! For some reason when I create the button in each department's workbook, the Selection.OnAction sets the pointer to my source code, not to the e-mail generation code I've embedded within each departmen't workbook.

I had a colleague try the button on one of the department workbooks and the macro tries to open my source code - it completely bypasses the embedded version

Art

[Norie]

Art

I can't see how running that code in a workbook which had the sub would cause Excel to look for the sub in an entirely unconnected workbook that isn't even open.

[c1802362]

I agree with you 100%. I am at a loss to figure out why this is occurring. Perhaps other users will chime in with suggestions...

[Norie]

Can you tell me exactly how you are running this code?

Also, where is it located, are any other workbooks open when you run it?

[c1802362]

How I'm running the code:

First some lead-in: Every month, I run a routine that takes the current month's aggregate data, reduces it, then places each department's section of the master data set on a new tab (Jan, Feb, Mar, etc) in each department's workbook. This code is located in my source file.

At the same time, my source code creates a button at the top of that month's sheet with the instructions to click the once the department certifies their content. Clicking the button launches the e-mail routine that sends me their certification. The e-mail code is local in the department's ThisWorkbook sheet. (As an aside, my system doesn't appear to recognize vbCR, vbCRLF, or vbNewline commands either when generating the e-mail)

So, the code runs stand alone from within each department's data workbook. No other workbooks need to be open. What I found out is that the button launches a request to open up my source file to access the e-mail generation routine. That will work fine for me, but causes an error for anyone working in the department file.

The department files are located in a directory on a server accessible to members of the departments. My source code is on a different server that has limited access.

Art

[Norie]

Art

Still not following.

Where do the tabs that contain the button created from the code you posted start out?

Is it in the master workbook?

[c1802362]

They start out in each department's workbook file.

The master dataset is generated monthly from my source code. Each department's piece of the master dataset is parsed out to a workbook named after the department. Each month of data has it's own sheet within the workbook. So, for example, There are workbooks named Sales, Marketing, Quality, Finance, etc. Each Workbook contains sheets "Jan", "Feb", "Mar", "Apr", for each month of data.

The button is added to the top of each month's worksheet at the time the new month's data is created (and that month's worksheet is created in the department's workbook)

Art

[Norie]

Art

Where is the code you posted being run from?

Also, that code doesn't refer to any worksheet or workbook.

[c1802362]

the code is being run from the source code file - the reason it does't reference any worksheet or workbook is that the sheet (target) is created, the data transferred to it, and the button added all while the target is active.

My original code activated the target worksheet before creating the button just in case, but it didn't work. I then tried to force the button assignment with

set targetsheet = ActiveSheet
Selection.OnAction = "targetsheet.CertifyEmail" (and variants like: target sheet & ".CertifyEmail")

and all I got were run-time errors.

So, I'm out of ideas

[Norie]

Where is the sheet created?

[Norie]

Art

I'm starting to think the problem might lie with the code you posted and specifically the lack of worksheet/workbook references.

How are you opening the target workbooks?

How are you referring to them in the code?

Can we see the rest of the code?

[Izandol]

You may include the workbook name in the OnAction property:

Please Login or Register  to view this content.

[Norie]

Art

You do realise the sub CertifyEmail should probably be in a standard module and not the ThisWorkbook module.

[c1802362]

Yes, I do (I was thinking I was going to be real clever)

I moved CertifyEmail to it's own moodule within each department's workbook. I also included the workbook name in the Selection.OnAction (and verified the correct workbook is called out)

Still - no luck - the system assigns the source location to the button, not the local code

I'm going to write a second macro to go through all of the department workbooks after the month sheet is created and assign the button directly

I'll let you know if that works

Art

[Norie]

Art

What code are you now using?

I thought you were already going through each workbook creating the button and assigning the macro?

[c1802362]

I just tried it - maybe I'm getting somewhere:

I made the reassign code as simple as possible:

1. the code is in a new book ("Book 1") as shown below
2. I open the target workbook and activate the sheet of the month I want
3. step through the code (I assigned the name 'cert' to the button when it was created)

Please Login or Register  to view this content.

The first time I tried to launch the Certify email routine after running through the above code, I got an error that said the system couldn't launch the e-mail code from another location. Subsequently. I tried to recreate the error message and lo and behold the button assignment is working - no error message.

So, I can make this work as a two step process, but it still isn't clear why the system is acting up

Art

[Norie]

Art

I think the problem might be the lack of referencing in the code.

If I was doing something like I would start by opening the target workbook(s) with code, creating a reference to it when it opens

Please Login or Register  to view this content.

The reference that's created can then be used in subsequent code whenever we need to refer to the workbook that's just been opened.

The code for adding the button would be in the same sub that opens the workbook.

Please Login or Register  to view this content.

You could have that code in a loop for all the target workbooks, and you could even change the names to the real thing.

[c1802362]

I always assign the references to workbooks and sheets as you've desribed (interestingly, we're using the same conventions - I normally name sources as wbkSource and wksSource and targets as wbkTarget and wksTarget)

What I noticed is you set btn, which is something I normally don't do.

In any event, it appears to be working (at least on the beta side), so I'll incorporate your code into the master source and try another beta run.

Thanks - BTW, I am marking this post as solved

Art

Please Login or Register  to view this content.

[Norie]

Art

Didn't see too many references in the original code you posted.

[c1802362]

No I stripped everything off to keep it simple

lesson learned!