Why simple Open_Workbook Macro works perfectly but error 91 when opened on another computer??

[marcelita03]

Hello guys
I am so frustrated. 2 days trying to decipher this.
I have emailed a spreadsheet to all my internal customers and they all get the same error

"Run-time error 91: Object Variable or with block variable not set"

We all work on Excel 2010... so I don't think that's the problem

When the file opens, a Open_Workbook Macro runs

Sub Workbook_Open()
Dim Sure As Integer
Dim sourceBook As Workbook
Dim Blank As Worksheet
Dim Current As Range
Set sourceBook = ActiveWorkbook
Set Blank = sourceBook.Sheets("Blank")
Set Accounts = sourceBook.Sheets("Accounts")
Set Current = Accounts.Range("l1")
Blank.Select
Sure = MsgBox("Click OK only to retrieve data for the first time this period (it might take a couple of minutes) otherwise click Cancel", vbOKCancel)
If Sure = 1 Then Filter Else
If Current.Value = True Then
Accounts.Select
Exit Sub

The error is in the line "Set Blank = sourceBook.Sheets("Blank")" but if I remove that it error in the next line...and in the next...and in the next

I don't get it, it works perfectly when I run it in my PC.

What is more puzzling....

When I take my spreadsheet and I e-mail as attachment to myself and I open it... I get the same error my customers get... so it's definitively not an Excel version issue...

After googling the issue extensively I am thinking now... maybe it has something to do with the fact that the e-mail changes the file to a "Read Only" and even after enabling editing on the file out of that e-mail there is something that is forever changed in the file extension or whatever and that prevents any "ActiveWorbook" reference from working?


Please help

 

[K e v i n]

Hi,
Might be worth trying the following change

Dim blank as Excel.Worksheet

Set Blank = Workbooks(Thisworkbook.Name).Worksheets("Blank")

 

[Michael M]

And also try

Blank.activate

I'd also change

Set Blank=

to

Set ws =

Might help AND reduce confusion in the code

 

[marcelita03]

Kevin I tried your change and the same thing happens..... it works.... but it breaks when the file is open from the e-mail

 

[marcelita03]

Michael,

I tried the change you suggested... and the same thing happens (as it happen with Nori and Kevin suggestions)
It works when I run it.

But once I send it over e-mail then the user opens it and gets the error.

Update: One user is telling me that if he saves the file, ignores the error (by clicking "end" in the error window prompt) , close the file and reopens it
then the macro runs correctly.

So the initial problem still persist... and it has to do with the fact that Outlook makes you "Enable Editing" the file to make any changes and that is messing up wiht the initial macro. I can tell all my users to click "end" in the error... but I would love to understand or find a way to get rid of it.

So if anyone has any other idea Im all ears. Thank you guys

 

[Michael M]

I may be a bit confused, but what's the line in Red doing...I get a compile error

Sub Workbook_Open()
Dim Sure As Variant, ws As Worksheet, ws1 As Worksheet
Dim Current As Range
Set ws = Sheets("Blank")
Set ws1 = Sheets("Accounts")
Set Current = ws1.Range("l1")
ws.Activate
Sure = MsgBox("Click OK only to retrieve data for the first time this period (it might take a couple of minutes) otherwise click Cancel", vbOKCancel)
If Sure = 1 Then Filter
Else
If Current.Value = True Then ws1.Activate
End If
End Sub

 

[marcelita03]

"Filter" is the name of another Macro I have recorded in the workbook

So if the user clicks "ok" the macro "filter" is called

 

[Michael M]

Ok, sorry...I didn't see that in the code..

 

[Norie]

I think the problem might be because of the security level settings the users who are receiving the email/workbook attachment have.

 

[marcelita03]

It might be. For what I have found online (no much) something in the extension of the file is altered when the document goes from [Read Only] to state and that prevents the Open_Worbook macros from selecting things correctly therefore the error. I am still in the look for a solution. Thanks guys!

 

[marcelita03]

Update: When users un-check the "Enable Protected View for Outlook Attachements" Under the Protected View menu in the Trust Center Settings of Outlook, then everything is solved. The macro runs as well in their PCs as in mine.
Not a perfect solution but that's what I'll have them doing from now on.

Thanks again everyone... all your suggestions were useful to reduce the macro and make it less crowded.