(Cross Post)
 

Hi Everyone,

First I would like to apologize for the cross posting - I am just not
sure where the right forum is for this question. I hope that it is
here!

I have found a problem that I am hoping to find a solution for. Simply
put, when I create an Excel.Application object with late binding, set
the .EnableEvents property to False and then open a workbook,
the .EnableEvents property is 'magically' set back to true. This
problem does not exist if I use early binding. Does anyone know how /
why this is happening and /or a way to get around it.

This is a problem for me because the workbooks that I am opening have
a form set to open on the WorkBook_Open event, and this form opens
behind all other visible forms and the Excel.Application object
displays nothing of course on the taskbar. My macro code stops running
while it waits for the form to be used / closed before continuing.
This is a problem that I would like to avoid. I do not understand why
changing to late binding is producing this different set of
behaviours.

To test this yourselves, just make a blank workbook, place a blank
form (doesnt need any controls for this experiment), and have it
display with the Workbook_Open event (do leave yourself a close
button!). Open the workbook with the following code (standard Module):

Sub test()

Dim XL As Object 'Excel itself
Dim WB As Object 'Workbook
Dim WS As Object 'Worksheets Collection
Dim ss As Object 'Spreadsheet (not set, just stays as object)

On Error GoTo ErrorHandler

Set XL = CreateObject("Excel.Application")

XL.DisplayAlerts = False
XL.Application.DisplayAlerts = False
XL.Application.EnableEvents = False
XL.EnableEvents = False

Set WB = XL.Workbooks.Open(<INSERT NAME.xls OF THE WORKBOOK
FILE HERE)

Set WS = XL.ActiveWorkbook.Sheets

For Each ss In WS
Debug.Print Filename & vbTab & vbTab & ss.Name
Next

Set WS = Nothing
WB.Close savechanges:=False

Set WS = Nothing
Set WB = Nothing
Set XL = Nothing

Exit Sub

ErrorHandler:
Debug.Print Error & vbTab & Err
Stop
Resume Next
End Sub

What you should see is that the application 'stops' running after the
Set WB = XL.Workbooks.Open line of code. It is actually still running,
just waiting for the form in the XL.Application object to be closed
before continuing. As you can see from the code above the EnableEvents
is definitely set to false, and if you watch the state of this
property in the XL.Application object you will see that it actually
changes state, all by itself, when the workbook.open method is called.
This does not happen with early binding - the EnableEvents property
stays as you put it, but it does change when using late binding and I
dont know why.

Can anyone help with this?

Excel 2000 SP3 build 9.0.8950

Cheers

The Frog

(Cross Post)
 

You could make the user form not modal -

UserForm1.Show (False)

Sam

" wrote:

Hi Everyone,

First I would like to apologize for the cross posting - I am just not
sure where the right forum is for this question. I hope that it is
here!

I have found a problem that I am hoping to find a solution for. Simply
put, when I create an Excel.Application object with late binding, set
the .EnableEvents property to False and then open a workbook,
the .EnableEvents property is 'magically' set back to true. This
problem does not exist if I use early binding. Does anyone know how /
why this is happening and /or a way to get around it.

This is a problem for me because the workbooks that I am opening have
a form set to open on the WorkBook_Open event, and this form opens
behind all other visible forms and the Excel.Application object
displays nothing of course on the taskbar. My macro code stops running
while it waits for the form to be used / closed before continuing.
This is a problem that I would like to avoid. I do not understand why
changing to late binding is producing this different set of
behaviours.

To test this yourselves, just make a blank workbook, place a blank
form (doesnt need any controls for this experiment), and have it
display with the Workbook_Open event (do leave yourself a close
button!). Open the workbook with the following code (standard Module):

Sub test()

Dim XL As Object 'Excel itself
Dim WB As Object 'Workbook
Dim WS As Object 'Worksheets Collection
Dim ss As Object 'Spreadsheet (not set, just stays as object)

On Error GoTo ErrorHandler

Set XL = CreateObject("Excel.Application")

XL.DisplayAlerts = False
XL.Application.DisplayAlerts = False
XL.Application.EnableEvents = False
XL.EnableEvents = False

Set WB = XL.Workbooks.Open(<INSERT NAME.xls OF THE WORKBOOK
FILE HERE)

Set WS = XL.ActiveWorkbook.Sheets

For Each ss In WS
Debug.Print Filename & vbTab & vbTab & ss.Name
Next

Set WS = Nothing
WB.Close savechanges:=False

Set WS = Nothing
Set WB = Nothing
Set XL = Nothing

Exit Sub

ErrorHandler:
Debug.Print Error & vbTab & Err
Stop
Resume Next
End Sub

What you should see is that the application 'stops' running after the
Set WB = XL.Workbooks.Open line of code. It is actually still running,
just waiting for the form in the XL.Application object to be closed
before continuing. As you can see from the code above the EnableEvents
is definitely set to false, and if you watch the state of this
property in the XL.Application object you will see that it actually
changes state, all by itself, when the workbook.open method is called.
This does not happen with early binding - the EnableEvents property
stays as you put it, but it does change when using late binding and I
dont know why.

Can anyone help with this?

Excel 2000 SP3 build 9.0.8950

Cheers

The Frog

(Cross Post)
 

Sounds good, so how would I detect if a form is open, and the name of
the open form?

This still doesnt get around the issue that code runs when late
binding is used. I dont control the incoming workbooks, so I must be
careful that any startup code doesnt change the data in the sheets. I
believe that disabling events is the only way to actually achieve this
and know with certainty that the workbook is unaltered by its
activation on opening.

Still cant figure out why this is happening.

The Frog