Open method used

H

Herminio

Hi fellows!
I need know what method was used to open my workbook, that is, a mous
click or a VBA workbooks.open.
Is there any way to find out it?
Thanks!
Hermínio
 
C

Chip Pearson

Herminio,

One way would be to use the Auto_Open event procedure to set a
flag variable. Because Auto_Open is not called executed when a
workbook is opened via VBA, it will run only when the workbook is
opened manually. E.g.,

Dim OpenByUI As Boolean

Sub Auto_Open()
OpenByUI = True
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
H

Herminio

Dear Chip.
Unfortunately I am using Excel 2000 and it looks like it does not
support the Auto_Open event.
The nearest one to that would be the Activate event, but this is
executed after the Open, in both manual and VBA calls.
Thank you anyway.
Regards,
Herminio.
 
T

Tom Ogilvy

In a standard module, create a sub with the name Auto_Open (don't do it in a
sheet module, thisworkbook module or a userform module).

Dim OpenByUI As Boolean

Sub Auto_Open()
OpenByUI = True
End Sub

It is supported in xl5 to xl2003.
 
H

Herminio

Dears Tom and Chip.
I've created a standard module with an Auto_Open subroutine, as yo
said, and, it’s true, that code is *only* executed when you open th
workbook manually (mouse click or Enter key).
Nevertheless the Auto_Open code is executed *after* the Workbook_Ope
subroutine. That's too late, because the code I want to bypass, in cas
of a "remote" VBA Workbooks.Open, resides inside the Workbook_Ope
subroutine. So, the problem remains unsolved.
Thank you any way.
Regards,
Herminio
 
D

Dave Peterson

If you want to avoid all of the workbook_open event, you could do this when you
open the workbook mechanically:

application.enableevents = false
workbooks.open filename:= ....
application.enableevents = true
 
T

Tom Ogilvy

Use only the Auto_open macro - move the code from the workbook_open event to
the Auto_Open event. Then when it is opened remotely, the code doesn't run.
(If I understand the situation correctly).
 
H

Herminio

Thank you, Dave.
I need bypass only part of the Workbook_Open code,
but it was nice to learn the new facility you've pointed out.
Regards,
Hermínio
 
C

Chip Pearson

Herminio,

Take all the code out of the Workbook_Open event procedure and
put it in the Auto_Open macro.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
H

Herminio

I'll try.
I have to check if my Workbook_BeforeClose subroutine will not presen
any new error in that case.
Thank you.
Regards,
Herminio
 
Top