Add button and module to open worksheet #2 from worksheet #1 with vba

[nmualum]

I have a worksheet controlled by another department that I'm working on 'improving' for my department. Without messing up the same worksheet that they use in their department.

So I have worksheet #1 , controlled by my department. There are a handful of fields/cells I want to automatically transfer over into worksheet #2 , controlled by another department. Once those cells are populated in #2 , I would like to then add a button to worksheet #2 that will generate a pdf of the active sheet in that worksheet and then email it to another user to approve.

So worksheet #2 is stored in SharePoint, and I don't have permission to modify that worksheet. But if I open that worksheet as a read only worksheet and keep it open on my desktop, I can add vba code to my worksheet #1 to send the necessary information to #2 . No problem with that.

But can I add vba code to #1 that modifies #2 , and adds a 'send email' button in a certain location, and create the module in #2 with the vba code to be executed by that newly added button?

So I'm adding the necessary functionality, without touching their original worksheet?

I found a thread on MrExcel for generating a pdf and sending the email, now I'm looking for how to programmatically add that to the second worksheet.

Thanks!

 

[JLGWhiz]

You need to clear up your terminology. Worksheets are contained in Workbooks. Worksheets and Sheets are interchangeable terms in Excel, but Worksheet and Workbook are not interchangeable. Some people refer to a Workbook as a Spreadsheet. While that too can be misleading, it does differentiate from a Worksheet, Sheet or Tab. I think you are referring to a workbook when you use Worksheet #1 and Worksheet #2 , but it needs to be confirmed to avoid confusion to responders like me.

 

[nmualum]

Thanks to WLGWhiz for pointing out that my original post was ambiguously worded. Hopefully this new version is a little clearer. I was unable to just edit the first post as it was after 10 minutes...

I have a workbook (destination.xlsm) controlled by another department that I'm working on 'improving' for my department. Without messing up the same workbook that they use in their department.

So I have workbook #1 (source.xlsm), controlled by my department. There are a handful of fields/cells I want to automatically transfer over into workbook #2 (destination.xlsm) , controlled by another department. Once those cells are populated in #2 , I would like to then add a button to workbook #2 (destination.xlsm) that will generate a pdf of the active sheet in that workbook and then email it to another user to approve.

So workbook #2 (destination.xlsm) is stored in SharePoint, and I don't have permission to modify that workbook. But if I open that workbook as a read only workbook and keep it open on my desktop, I can add vba code to my workbook #1 (source.xlsm) to send the necessary information to #2 (destination.xlsm). No problem with that.

But can I add vba code to #1 (source.xlsm) that modifies #2 (destination.xlsm), and adds a 'send email' button in a certain location, and create the module in #2 with the vba code to be executed by that newly added button?

So I'm adding the necessary functionality, without touching their original workbook?

I found a thread on MrExcel for generating a pdf and sending the email, now I'm looking for how to programmatically add that to the second workbook.

Thanks!

 

[JLGWhiz]

I think the keeper of the shared file would have to approve the installation of any code into the shared workbook. If it is approved then the code would need to be added by whoever has permissions to do such additions. But all other users should be able to install the code for exporting to PDF and emailing on their workstations without interferring with the shared file.