Author Topic: Force enable embedded macro-enabled worksheet  (Read 433 times)

0 Members and 1 Guest are viewing this topic.

rjloring

  • Newbie
  • *
  • Posts: 1
Force enable embedded macro-enabled worksheet
« on: October 19, 2022, 10:25:44 PM »
First of all, I have embedded the worksheet by doing "Paste Special -> Paste as Microsoft Excel Worksheet (code)" (not as a link). I did this so that I can make use of the spreadsheet function of Excel as a calculation sheet by accessing it through Visio. But I want this Excel file to be accessed ONLY through the embedded object in Visio so that I won't have to copy and paste 2 files (Visio, source Excel file) whenever I move or share this. (This Excel file already contains VBA functions that are used within the calculation sheet).
I've also already programmed this embedded Excel to open through Visio VBA.

After I do this, two scenarios happen when I try to open the embedded Excel object (via double click or running Visio VBA):
1. When the source file of the embedded Excel object is open in the background, the Security Notice gives me the options "Enable Macros" or "Disable Macros". Once I enable it, everything works fine.
2. But when the source file is closed and I try to open the embedded Excel object, the Security Notice gives me only one option "Disable Macros". And so, I won't be able to make use of the macros within the Excel file.

Is there any way to resolve the issue for the 2nd scenario?
I don't want to do the 1st scenario since it would just defeat my original purpose.

I'm still a newbie on Visio programming so I would appreciate any help!

P.S. I'm only using Visio Standard