VBA Form issues

[cstimart]

I'm encountering a couple form issues with my current work environment using Excel 2007. Our systems are locked to ALWAYS prompt the user to enable any/all macros.

I am creating a central template that I can use to open and run various reports that I have created. (Eventually, I'd like to have all the macros/modules with the template and not have to open other files...but in the meantime...)

1. When the template opens another file and the security prompt is supposed to appear, my Form appears instead, which causes me to cancel and re-select the report.
2. When the report is done and closes out and the template becomes the active file, I'd like the form to reappear, however is does not. I have tried utilizing Private Sub Workbook_Activate(), Private Sub Workbook_Open() and Private Sub Workbook_SheetActivate(ByVal Sh As Object) with no luck.

Any ideas or solutions?

 

[GlennUK]

Are you saying that there is some VBA in the template that opens another file? And you want the security prompt to appear?

Can you explain why you want the security prompt to appear?

 

[cstimart]

Yes, my template opens another excel file. When that file is opened and the excel security prompt is supposed to appear, asking the user if they want to enable to content, my userform from my 1st excel file re-appears.

I then have to use the cancel button and then close the 2nd excel file. I then call the template/form and re-select a file and I get the security prompt and not the userform.

 

[GlennUK]

Why do you want the security prompt to appear in the middle of a macro when you opening a workbook in that macro? For what purpose? Are you simply trying to prevent any workbook_open event macro from running?

 

[cstimart]

I don't want it to, but that's how excel is set up at work. Any new file that opens with a Macro will provide that prompt...and our IS dept has that setting locked, so I can't change it.

 

[GlennUK]

Yes, my template opens another excel file. When that file is opened and the excel security prompt is supposed to appear, asking the user if they want to enable to content, my userform from my 1st excel file re-appears.I then have to use the cancel button and then close the 2nd excel file. I then call the template/form and re-select a file and I get the security prompt and not the userform.

So, you don't what the security prompt to appear ( even though your wording, above, makes it seem as though you do ). And you also don't want any auto macros to run ... by default Excel doesn't do that. Show the code that opens the workbook.