Enable macros after Workbooks.Open

[postal302]

Hello all,

I understand similar questions to this have been asked a few times over, but I still haven't been able to find a solution to my problem. I will try to pose my question in a different way than has been asked before.

I have two separate workbooks both with embedded macros in them. Both workbooks must have macros enabled to function properly for the users. Workbook-A has a command which will open Workbook-B (if it's not already open) and run a macro in Workbook-B.

My problem is after Workbook-A runs the Workbooks.Open command, it just continues on with the script and bulldozes right past the "Enable Macros" options for Workbook-B causing macros to be disabled and rendering the functions of Workbook-B inaccessible. What I need to do is pause at this point and allow the user to select "Enable Macros" so Workbook-B will function properly, and then continue on with the macro.

I understand there is no way to force macros to run when opening another workbook, and I don't want to do that anyway. Also the administrators have the security settings locked so users cannot automatically enable macros, so unless there is an accepted digital signature, a user must click the "enable macros" settings upon opening workbooks or macros will be blocked. I don't want to rely on the digital signature method because many users here have a hard enough time with the basic functions of excel and will readily click "No" or "Deny" on any questions they don't understand.

Does anyone know a solution to this or a good work around which will enable macros in Workbook-B and then continue with the macro?

Sorry for the length but thanks in advance.</SPAN>

 

[rjwebgraphix]

The following code will show you how to pause. There may be more needed besides this to get it to work right, but this could be a start. There could also be better ways of accomplishing the same task.

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub TestPause()

MsgBox "There will be a 2000ms pause between when you click ok and the next msg"

Sleep 2000

MsgBox "It paused for 2000 milliseconds, increase or decrease the number to pause for longer or shorter time periods"

End Sub

 

[JoeMo]

Why not add the file location of Workbook B to your trusted locations? Office Button>Excel Options>Trust Center>Trusted Locations.

This will eliminate the need to enable macros when the workbook is opened.