enable mail recipient to exploit macro in excel attachment

[MagicMan]

Hi Folks,

I have a workbook that I extract a sheet from and send to a recipient on a regular basis. They mark-up that sheet and return it to me.

I use a macro to extract the spreadsheet and send it to the recipient. - No problems, that works fine.

I'd like to include a button (the green one in the example) on the spreadsheet that enables the user (in this case the 'secondary' user) the press the button and it activates the macro. at the moment I keep getting it cross reference back to the original file that I did the original extract from.

I thought I'd cracked it, but due it's difficult to test as the cross reference I mentioned previously, is true.

Here's the VBA that is in the Sheet Code:

Please Login or Register  to view this content.

[bulina2k]

You could try the following code for reassigning the macro to the Rectangle 1 button:

Please Login or Register  to view this content.

The code should be in Workbook_Open() event.

[bulina2k]

Another approach would be to save the whole workbook as a new file, and then delete the sheets you don't need to send.
That way you keep the macros and the refrences in place.

[MagicMan]

Thanks Bulina,

I don't think I can do your second approach, because the content is generated through a series of index, match and VLookup from other sheets from my "master" workbook. Deleting the sheets would then delete the content.

Now, I'm going to be "proper dense" here Mindful that the sheet that is sent to the recipient is 'created' from my "Master" workbook, do I enter it on the relevant sheet on my workbook and then it will 'follow-through' as it were with each 'creation'?

and I kind of understand in principle what you mean when you say it should be in the Workbook_Open() event. But, again, showing my 'denseness' how is that done in reality?

[bulina2k]

Try inserting this code after ActiveSheet.Copy :

Please Login or Register  to view this content.

so the entire code would be:

Please Login or Register  to view this content.

[MagicMan]

Sorry folks, deleted this one to create new thread and prevent confusion (mostly on my part!)