Workbook_Open() Not Working for Other Users, But Works for Me!

[Bayport_Mama]

I have a workbook that is saved in a public network folder for others to open. Whenever I open the workbook (as if I were an end user), the code works flawlessly, however, if anyone else at my work tries to open it, they immediately get Run-time error '1004' Application-defined or Object-defined.

My code is stored in the ThisWorkbook object. Here's my code:

Private Sub workbook_open()

MsgBox "Update in progress, hang tight! ", vbOKOnly

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
Worksheets("Completed Reviews1").Range("A:F").ClearContents
Worksheets("Completed Reviews2").Range("A:F").ClearContents
Application.DisplayAlerts = False
Application.CutCopyMode = False
Application.EnableEvents = False

Workbooks.Open ("S:\locationoffile")
Workbooks("Information 2.xlsx").Worksheets("Sheet2").Range("a1", Range("a1").End(xlDown).Offset(0, 5)).Copy

ThisWorkbook.Worksheets("Completed Reviews2").Range("a1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False


Call Update_Officer_Reviews

Workbooks("Information 2.xlsx").Close

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.DisplayStatusBar = True

MsgBox "Update complete." & vbCrLf & vbCrLf & _
"Please select your name from the" & vbCrLf & _
"dropdown list to the left.", vbOKOnly

End Sub

Sub Update_Officer_Reviews()

Application.CutCopyMode = False

Workbooks("Information 2.xlsx").Worksheets("Sheet1").Select
Workbooks("Information 2.xlsx").Worksheets("Sheet1").Range("a1", Range("a1").End(xlDown).Offset(0, 5)).Copy

ThisWorkbook.Worksheets("Completed Reviews1").Range("a1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
ThisWorkbook.Worksheets("Current Reviews").Activate

End Sub

Anyone have suggestions on how to fix this??? I'm so confused as why it would work for me, but not others at my work.

 

[Bayport_Mama]

Protected view is different. When you first open a workbook from a remote source, it will often open in a protected view and the user will be prompted to enable content. When they enable it for the first time, any code in the Workbook_Open event that accesses Application properties and methods will fail.

I just had my coworker try it again, and Excel is not prompting her to Enable Content at workbook opening. After the code attempts to run (and fails) she then can choose to enable content, but then it appears the macros are blocked!

I think we may have found the problem!!!

Now....how do we fix that???

 

[Joe4]

Did you check her Macro Settings under "Trust Center Settings" on those computers to see what it is set at?

 

[Bayport_Mama]

Did you check her Macro Settings under "Trust Center Settings" on those computers to see what it is set at?

View attachment 73520

It appears that her computer was set to Disable VBA macros with notification (like your screen shot you sent me). She tried changing it to Enable, and it still doesn't work.

The file location is a location where all of our other macro enabled workbooks are stored, so it's a trusted location.

Any other ideas??

 

[Joe4]

Note that ALL those macro settings are by user, not by file.
So have her try adding the Trusted Location and updating the Macro Settings.
Then have her try re-opening the file.

 

[RoryA]

I just had my coworker try it again, and Excel is not prompting her to Enable Content at workbook opening. After the code attempts to run (and fails) she then can choose to enable content, but then it appears the macros are blocked!

I think we may have found the problem!!!

Now....how do we fix that???

Code shouldn’t be running prior to being enabled - that should be impossible!

 

[Bayport_Mama]

Code shouldn’t be running prior to being enabled - that should be impossible!

I guess I'm not sure what's going on??? It's definitely running the code to recognize that I have a message box at workbook_open and then she gets the Run Time Error 1004 at the copy range stage in

Workbooks.Open ("S:\locationoffile")
Workbooks("Information 2.xlsx").Worksheets("Sheet2").Range("a1", Range("a1").End(xlDown).Offset(0, 5)).Copy

According to our IT Department, they said that the location where all of our Macro Enabled Workbooks are saved, it is globally recognized as a Trusted Location, and all workbooks should be trusted so no macros are blocked. However, this workbook seems to continue to be an issue and go against what our IT dept. says.

I had my coworker add the source location manually as a Trusted Location, and the workbook STILL doesn't work. We even selected "Allow Trusted Locations on my Network" to see if that helped, which it did not.

Thank you both for being patient with me on this!!! Our IT dept refuses to help with this because they don't like working with macros, so I'm reaching for help.

 

[Joe4]

Can you run a simple macro from that workbook?
Something like:

Sub Test()
    MsgBox "This is a test!"
End Sub

If so, then copy the code you have in your "Workbook_Open" event, and put it in a manually called procedure, and then try running it manually, one line at a time (using the F8 key) and see where it runs into problems.

 

[RoryA]

That copy line is wrong. It should be:

Workbooks("Information 2.xlsx").Worksheets("Sheet2").Range("a1", Workbooks("Information 2.xlsx").Worksheets("Sheet2").Range("a1").End(xlDown).Offset(0, 5)).Copy

 

[Bayport_Mama]

Can you run a simple macro from that workbook?
Something like:

Sub Test()
    MsgBox "This is a test!"
End Sub

If so, then copy the code you have in your "Workbook_Open" event, and put it in a manually called procedure, and then try running it manually, one line at a time (using the F8 key) and see where it runs into problems.

This is the problem.

When I run it, whether when opening the workbook or manually line by line, I personally have no issues with the code. It's when others try to open it, that's when the issues pop up.

Our department has been using XLSM workbooks for quite some time with little issues. Within the past month, it seems as though more and more issues are popping up.

This situation, however, is different. It's a brand new workbook. Existing file location (that has been trusted in the last few years). I've removed all the protection possible and changed the macro security, with no luck.

 

[Bayport_Mama]

That copy line is wrong. It should be:

Workbooks("Information 2.xlsx").Worksheets("Sheet2").Range("a1", Workbooks("Information 2.xlsx").Worksheets("Sheet2").Range("a1").End(xlDown).Offset(0, 5)).Copy

That's it!!! Wow....all this back and forth and it was a simple mistake on the copy line.

THANK YOU BOTH SO MUCH!!!