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

 

[Norie]

Is sourcebook the workbook the code is in?

If it is then try omitting it from the code.

Set Blank = Sheets("Blank")
Set Accounts = Sheets("Accounts")

 

[marcelita03]

Hi Norie, the code is within "ThisWorkbook" (I got there by opening "ThisWorbook" icon in the VBAProjectExplorer window.
that way it runs the macro automatically when the file is open by any user.

I will remove the SourceBook per your suggestion to see if that works. I will let you know in 2 minutes

 

[marcelita03]

I tried your solution an adjusted the Open_Worbook code to:

Sub Workbook_Open()
Dim Sure As Integer
Dim Blank As Worksheet
Dim Current As Range
Set Blank = Sheets("Blank")
Set Accounts = 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
Else
DeleteRows
Worksheet_Activate
Filter
End If
End Sub

Once again, the macro runs without a hitch in my computer.
But once I send it by e-mail the user gets an error
This time the error is Run-time error '1004' : Method 'Select of Object_Worksheet' Failed
when I "debug" it highlights the 'Blank.Select' line in the code


The only thing I am trying to do here is have users looking at the blank tab when they open the file, because there might be data in the other two tabs ("accounts" and "markets") that I don't want them to see.
After the "blank" tab is selected, the prompt window ask the user a question and depending on the answer, another macro runs replacing data in Accounts and Markets.

and like I said, everything works correctly... until the spreadsheet is e-mailed out... then the code stops working.

 

[marcelita03]

I tried your solution an adjusted the Open_Worbook code to:

Code:

Sub Workbook_Open()
Dim Sure As Integer
Dim Blank As Worksheet
Dim Current As Range

Set Blank = Sheets("Blank")
Set Accounts = 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 Else

DeleteRows 'this is calls another macro1
Worksheet_Activate 'this is calls another macro2
Filter 'this is calls another macro3

End If

End Sub

</PRE>

Once again, the macro runs without a hitch in my computer.
But once I send it by e-mail the user gets an error
This time the error is Run-time error '1004' : Method 'Select of Object_Worksheet' Failed
when I "debug" it highlights the 'Blank.Select' line in the code


The only thing I am trying to do here is have users looking at the blank tab when they open the file, because there might be data in the other two tabs ("accounts" and "markets") that I don't want them to see.
After the "blank" tab is selected, the prompt window ask the user a question and depending on the answer, another macro runs replacing data in Accounts and Markets.

and like I said, everything works correctly... until the spreadsheet is e-mailed out... then the code stops working.

 

[Norie]

What happens if you change Blank.Select to this?

Application.Goto Select.Range("A1")

 

[marcelita03]

I'll try... but ...how would the macro know that I want it to go to the tab named "Blank"?

 

[marcelita03]

I get a "Compiled Error... Expected: Expression"

 

[Norie]

Oops, it should have been this.

Application.Goto Blank.Range("A1")

 

[marcelita03]

Hi Nori, thanks for sticking with me

Once again, your code works but once the file is emailed... the users:

1. Open the attachment from the email (Outlook 2010)
2. The file opens
3. The user sees the common Outlook message at the top "Protected View: This file is originated as an e-mail attachement" and "Enable Editing"
4. The user clicks "Enable Editing" button (Common procedure to open a excel attachment from an email)


Now it gives me a Run-Time Error 1004: "Method Goto of Object Application Failed"


Continue, End , Debug


When click on "Debug"


the Application.Goto Blank.Range("A1")

is highlighted


I can't understand this