Click here to Skip to main content
14,578,158 members
Rate this:
Please Sign up or sign in to vote.
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
Updated 19-Mar-20 8:18am
v2
Rate this:
Please Sign up or sign in to vote.

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.
   
Rate this:
Please Sign up or sign in to vote.

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
   
Rate this:
Please Sign up or sign in to vote.

Solution 3

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.
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100