Workbook open fail problem

[plihu]

Hi,

I've noticed strange problem with sub Workbook_open,
here's fragment of the code

Private Sub Workbook_Open()
UserWBName = Me.Name
Sheets("Statistics").DTPicker1.Value = Now
Call Import
End Sub

What is imprtant-that Workbook_Open sets string UserWBName the name of workbook and calls procedure Import.
And here's fragment of Import (in different module)

Sub Import()
userWSName = "Statistics"
Workbooks(UserWBName).Sheets(userWSName).Cells(1, 1).Activate
End Sub

Everything is ok, and works fine but the first time when user launch this Excel file.
Then the "yellow label" shows, when user has to enable macros to make it work. If he do so-he receive Run-time error '1004' Activate method of range class failed.
And this is ONLY for the first time, because the next time - when this file is trusted, and yellow label does not show - everything works fine.


Btw I've done some testing and noticed that when I put e.g. application.wait for 2 seconds in Workbook_Open() procedure it also does not work for this first time with yellow label.
I work in big company and I can't enable all macros without notification for all users.


Any suggestions?
thanks for help!

[Aflatoon]

First, it would be easier to just ThisWorkbook.
Second, you need to ensure that sheet is active before you can select a cell on it:

With ThisWorkbook.Sheets(userWSName)
.Activate
.Cells(1, 1).Activate
End With

Third, it would be better to call your startup code using Application.OnTime from the Open event, rather than calling it directly.

[plihu]

I've tried it but it still does not work.
There's an error when it tries to activate Sheet - ThisWorkbook.Sheets(userWSName).Activate
And still - it only happens for the first time when the user has to accept "yellow label". Second time-macro works just fine.

[Aflatoon]

Using OnTime, or not?

[plihu]

Yes,
Here's how I've done it:

Private Sub Workbook_Open()
UserWBName = Me.Name
Sheets("Statistics").DTPicker1.Value = Now
Application.OnTime Now + TimeSerial(0, 0, 1), "Import"
End Sub

and the errors occurs Run-time error '1004': Method 'onTime' of object '_Application' failed.

[Aflatoon]

Is it just the Enable Macros dialog you get, or does the workbook open in Protected View initially?

[plihu]

Here's dialog I get.
yelow label.jpg
Once user click Enable Editing button it starts Workbook_Open() procedure... and fails

[Aflatoon]

Ah. I think this is a bug - I have seen it myself a couple of times and logically it shouldn't happen. I'll try and get it filed if it isn't already.

[plihu]

Ok,

that's what I thought,
Thanks for help!

[SamT]

Private Sub Workbook_Open()
DoEvents
Continue
End Sub

Private Sub Continue()
UserWBName = Me.Name
Sheets("Statistics").DTPicker1.Value = Now
Import
End Sub