Enable/disable 'security warning' box in EXCEL.

Question

0.00/5 (No votes)

See more:

In my Visual Basic(VB6)File, I have 3 OLEs.(Excel as object in all.) They are opened as Book1,Book2 &Book3.
Data given in my VBfile are passed on to Book1, some calculations are done there and then some information from Book1 goes to Book2. In Book2, a large amount of code is written in its VBA using VBEditor.
This code runs in the event of "New Sheet add". This event is triggered from my VB6 file.
After the code runs, the results are sent to Book3 and that is exhibited for reading. Book1 and Book2 are not made visible, they work in background.
I use Office 2007/Windows 7. Macros are 'Disabled with notification" in the Trust Centre Settings.

My problem:-
The security warning box asking to enable macros does not appear on the screen. Perhaps it lies in the background.
The program hangs when my VB6 code reaches the stage of opening Book2 (in which vba code is there) for transferring data from Book1 to Book2. Message "Application busy...switch to.. etc." comes on the screen when I try to click the screen. If I open any other outside excel file at that point, the warning box to enable macros appears.
Then macros enabled, code in Book2 vba works and vba error messages appear. These errors do not occur if macros are enabled in Trust Centre settings, then these code works fine and finally Book3 appears with end results.
Sometimes when the outside excel file is closed, message "Excel has stopped working...etc" comes.
Is there anyway to force the security warning box to appear on the front of screen automatically so that I can enable macros for that session ?

What I have tried:

This problem is with reference to OLE only. Other outside excel files (not connected with VB6 file) with vba code gets opened easily (with the trick of making all worksheets veryhidden/one sheet visible etc..etc..) and the warning box appears on the screen. No problem then. All my OLE objects are .xlsm type. Through VB6, the outside .xlsm files get open easily even if the macros are disabled. Problem comes with OLE excels only.

Posted 16-Mar-20 22:58pm

Member 13314240

Updated 19-Mar-20 8:18am

Richard MacCutchan

v2

Add a Solution

3 solutions

Solution 1

You can disable it, but i do NOT recommend to do that!

See:
Enable or disable security alerts on the Message Bar - Office Support[^]
Enable or disable macros in Office files - Office Support[^]

If you have any macro to made some calculation, i'd suggest to re-write it to your VB project.

Posted 16-Mar-20 23:29pm

Maciej Los

Solution 2

Your problem is that the Workbook containing the macros is not visible - nor is the "session" in which it is being opened, visible.

Because you are using 3 OLE objects you essentially have 3 instances of Excel, whereas when you try the same thing in VBA, the workbooks are all opened in the same instance of Excel - and the message is therefore displayed.

It seems to be a very strange model you are using - is there any real need for three workbooks instead of just one? You can utilise the hidden sheet trick just as easily

Posted 17-Mar-20 2:58am

CHill60

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 13314240 · Accepted Answer · 2020-03-19T08:19:00

I myself could figure out a round about way to solve this problem as follows:-
A dummy excel file in the VB6.File application path was created. Before opening
Book2, that dummy file is forcefully opened/closed to trigger the security
warning box to appear on the screen, out front. Code used in VB6 is:-

Dim NConnect As FileDialog
Set NConnect = Application.FileDialog(msoFileDialogFolderPicker)
Set xlApp = New Excel.Application 'This file is opened only to force the vb to make the security warning box visible in the screen.
xlApp.Visible = True
Set wb = xlApp.Workbooks.Open(App.Path & "\dummy.xlsx")
wb.Application.DisplayAlerts = False
wb.NewWindow.Visible = True
wb.Save
wb.Close
xlApp.Quit
Set xlApp = Nothing
Set wb = Nothing

Thank you for the quick response/solutions offered. I am interested in alternate
solutions to this problem and also any thoughts on my workaround.