View Full Version : Source workbook keeps opening when Destination workbook is protected?

10-11-2012, 09:20 AM
Hi everyone,

I have been working on a project all day but have been stumped with this problem all day:

I have a source workbook which will be used by a member of HR to track training undertaken by employees. I have made a separate workbook that combines certain aspects of the Source workbook and puts it into a table for the Team leaders to see to be able to view which members of their team still require training.

It is all working perfectly, including refreshing the destination workbook on opening of the file. Obviously because it contains formulas linked to the source workbook, I don't want the team leaders to be able to alter any information in the destination book. I only want them to be able to look at it and scroll up and down/ side to side.

However, when I protect the destination worksheet and test it's protection by double clicking somewhere on the table, the correct message pops us saying it's protected, BUT THEN SWITCHES TO THE SOURCE WORKBOOK even if it wasn't open!

Can anyone help with this?? It's starting to drive me crazy as it's the only thing stopping me from passing it onto HR to start using!


Many thanks


10-13-2012, 08:29 PM
Hi Sam,

My understanding is that Excel will open the SOURCE file to update the references in the DESTINATION and close it invisibly. If this is disrupted it leaves the file open and visible. It may be better to open the source file invisibly yourself when you open the DESTINATION file and close it on workbook close. e.g. for workbook_open (untested)

Dim wSource As window

Workbooks.Open ("Filepath\filename")

For Each wSource In Windows
If wSource.Caption = "filename" Then
Windows("filename").Visible = False
End If
Next wSource