Excel setting

[Hanz77]

Following is a quick summary of my set up followed by the problem I’m seeing.

When a user opens spreadsheet1 it automatically opens spreadsheet2 in read-only mode using Autpen.

Spreadsheet1 contains several cells where users enter comments. Once the comments are entered there is a button that triggers a vb script that closes spreadsheet2 and then reopens spreadsheet2 in read-write mode. The comments from spreadsheet1 are then copied to spreadsheet2. Spreadsheet2 is then saved, closed and then opened again in read-only mode. (I try to limit the amount of time spreadsheet2 is in read-write mode as there could potentially be multiple people trying to save their comments at the same time. This method prevents me from having to deal with a shared workbook.)

Spreadsheet1 also contains formulas that pulls in previous comments from spreadsheet2.

Now for the problem I am having. For some users, spreadsheet2 that opens in the background is not being recognized by the formulas in spreadsheet1 and returning #VALUE! errors. Also, the formulas are showing the absolute path of spreadsheet2 rather than just spreadsheet2.xls.

It seems like there is a setting somewhere that is opening spreadsheet2 up in a separate Excel session, which would explain why the formulas in spreadsheet1 is treating spreadsheet2 as a closed workbook. Again, this only affects some people. This process works perfectly for most people. Any thoughts on which setting may be causing this to happen or if I’m completely overlooking something else?

 

[RoryA]

What is the code that opens the second workbook?

 

[Hanz77]

Sub Autpen()

On Error Resume Next
Workbooks("spreadsheet2.xlsm").Activate
If Err = 0 Then
GoTo AlreadyOpen
End If
Err.Clear
Workbooks.Open Filename:="C:\Folder1\ spreadsheet2.xlsm", UpdateLinks:=0, ReadOnly:=True

AlreadyOpen:

On Error GoTo 0
Workbooks("spreadsheet1.xlsm").Activate


End Sub

 

[RoryA]

Odd. I don't know of any setting that would cause workbooks.open to create a new instance of Excel.