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.

 

[Joe4]

Welcome to the Board!

If they hit "Debug" whenthey get that error message, what line does it show?

I am willing to bet it might be this one:

Workbooks.Open ("S:\locationoffile")

If they don't have a similar "S" drive mapped on their computer, that line would cause an issue.

 

[Bayport_Mama]

Welcome to the Board!

If they hit "Debug" whenthey get that error message, what line does it show?

I am willing to bet it might be this one:

Workbooks.Open ("S:\locationoffile")

If they don't have a similar "S" drive mapped on their computer, that line would cause an issue.

Hi Joe4.

Because the workbook is in Read Only, they do not get the Debug option. They can only select "End".

They all have access to the S:drive file that I have in my code (I put a fake file name in this post). This is why I'm confused by it!!

 

[Joe4]

Because the workbook is in Read Only, they do not get the Debug option. They can only select "End".

I don't think you mentioned that it is "Read Only" before. That is a pretty important detail.

If the workbook is truly Read-Only, then these lines would fail:

Worksheets("Completed Reviews1").Range("A:F").ClearContents
Worksheets("Completed Reviews2").Range("A:F").ClearContents

as thery are trying to clear data from those columns.

So, of course I would expect errors, as it cannot update cells on a read-only file!

 

[Bayport_Mama]

I don't think you mentioned that it is "Read Only" before. That is a pretty important detail.

If the workbook is truly Read-Only, then these lines would fail:

Worksheets("Completed Reviews1").Range("A:F").ClearContents
Worksheets("Completed Reviews2").Range("A:F").ClearContents

as thery are trying to clear data from those columns.

So, of course I would expect errors, as it cannot update cells on a read-only file!

Sorry about that!

However, when I open the file in "Read Only" the code works just fine! Others are doing exactly what I'm doing but they have the issues.

How do I go about fixing this? Ideas?

 

[Joe4]

OK, actually, in thinking about this further, read-only would not prevent them from making changes to the file, it only prevents them from re-saving the file with the same name.
However, I did some testing, and the "Debug" option STILL does work on a read-only file. That should have no affect on it.
Did you confirm that that actually cannot click the "Debug" button, or are you just going on what they tell you (I ask because many users get confused)?

Also, this isn't a shared file that someone else already has opened, is it?
And they aren't trying to run it from Excel on-line, are they?
And is everyone running the same version of Excel on their computers?

 

[Bayport_Mama]

OK, actually, in thinking about this further, read-only would not prevent them from making changes to the file, it only prevents them from re-saving the file with the same name.
However, I did some testing, and the "Debug" option STILL does work on a read-only file. That should have no affect on it.
Did you confirm that that actually cannot click the "Debug" button, or are you just going on what they tell you (I ask because many users get confused)?

Also, this isn't a shared file that someone else already has opened, is it?
And they aren't trying to run it from Excel on-line, are they?
And is everyone running the same version of Excel on their computers?

Yep. They sent me a screen shot of the error message they received (attached).

Not a shared file.
Not running from Excel-online (I have been literally running back and forth from my office to theirs to watch what they are doing).
As far as we can tell, we all have the same version of Excel, which is Microsoft 365, Version 2208.

 

[RoryA]

Does it open in protected view for them originally?

 

[Bayport_Mama]

Does it open in protected view for them originally?

With working with Joe4 above, I've tested a few different things.

1. I've removed the Read Only protection. Still has an error on their end (but works for me).
2. I've removed the Protect Workbook feature. Still has an error on their end (but works for me).

I still have the worksheets protected.

 

[RoryA]

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.