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

nmualum

New Member
Joined
Jul 26, 2017
Messages
9
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!
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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.
 
Last edited:
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top