Workbook_Open command fails and blows up all cell formulas

[pdberger]

Good morning --
On opening, my workbook runs a sub called 'Reflinks' (listed below) that
opens a file on a company server and downloads a lot of reference values.
About 20 managers have copies of the workbook in offices across the
northwest, and they all open up and retrieve this reference data. The
workbook consists of two data pages, three calculation pages, and one final
'presentation' page. There are lots of references between the pages, as well
as to pull the reference data from our server.
One of the managers opened up her workbook, and got an error code 1004 --
'the specified dimension is not valid for the current chart type'. Then,
EVERY cell on EVERY page that had a reference to another page got changed to
look for the data from the server, rather than the correct page in the
workbook. So, if the formula said "='Page1'!A1", it was automatically
changed to 'ftp://[Instrument Panel Reference Data]'Page1'!A1"! For hundreds
of cells. And all the calculation cells on all pages are password-protected,
but it still happened anyway. Here's the code:

Sub RefLinks()
Dim wkb As String

'Refresh Links
Sheets("Other Calculations").Select
ActiveSheet.Unprotect ("MedMan")
wkb = ActiveWorkbook.Name

' ActiveWorkbook.ChangeLink Name:= _
' "ftp://[email protected]/Peter/Instrument Panel
Reference Data.xls" _
' , NewName:="Instrument Panel Reference Data.xls", Type:=xlExcelLinks
ActiveWorkbook.ChangeLink Name:="Instrument Panel Reference Data.xls", _
NewName:="Instrument Panel Reference Data.xls", Type:=xlExcelLinks
Workbooks.Open Filename:= _
"ftp://mariantoo.medman.com/Peter/Instrument Panel Reference Data.xls"
Windows(wkb).Activate


ActiveSheet.Protect ("MedMan"), DrawingObjects:=True,
Contents:=True, Scenarios:=True
End Sub

Thanks in advance for any help you can provide. I don't relish fixing 100+
cells, especially if I don't know why it blew up in the first place.

 

[JLGWhiz]

It seems to me that if all other workbooks are functioning as designed, that
the one which does not function properly has a different condition than the
others. If that is true, then that system would require individual analysis
to determine what is different to cause the cells to reference the ftp link
as part of the workbook reference. Apparently the basic code works.

 

[pdberger]

Thanks for the prompt response. Unfortunately, she's in Alaska so I can't
just wander over. Things worked fine until, for no reason that we can
figure, it just blew up and rewrote everything. So perhaps two questions:

1) Is there some way I can protect the sheets, cells, and graphs so that
the only possible way to change them is manually?
2) In looking for something different in her setup, is there any place we
can start first?

Thanks in advance.

It seems to me that if all other workbooks are functioning as designed, that
the one which does not function properly has a different condition than the
others. If that is true, then that system would require individual analysis
to determine what is different to cause the cells to reference the ftp link
as part of the workbook reference. Apparently the basic code works.

Good morning --
On opening, my workbook runs a sub called 'Reflinks' (listed below) that
opens a file on a company server and downloads a lot of reference values.
About 20 managers have copies of the workbook in offices across the
northwest, and they all open up and retrieve this reference data. The
workbook consists of two data pages, three calculation pages, and one final
'presentation' page. There are lots of references between the pages, as well
as to pull the reference data from our server.
One of the managers opened up her workbook, and got an error code 1004 --
'the specified dimension is not valid for the current chart type'. Then,
EVERY cell on EVERY page that had a reference to another page got changed to
look for the data from the server, rather than the correct page in the
workbook. So, if the formula said "='Page1'!A1", it was automatically
changed to 'ftp://[Instrument Panel Reference Data]'Page1'!A1"! For hundreds
of cells. And all the calculation cells on all pages are password-protected,
but it still happened anyway. Here's the code:

Sub RefLinks()
Dim wkb As String

'Refresh Links
Sheets("Other Calculations").Select
ActiveSheet.Unprotect ("MedMan")
wkb = ActiveWorkbook.Name

' ActiveWorkbook.ChangeLink Name:= _
' "ftp://[email protected]/Peter/Instrument Panel
Reference Data.xls" _
' , NewName:="Instrument Panel Reference Data.xls", Type:=xlExcelLinks
ActiveWorkbook.ChangeLink Name:="Instrument Panel Reference Data.xls", _
NewName:="Instrument Panel Reference Data.xls", Type:=xlExcelLinks
Workbooks.Open Filename:= _
"ftp://mariantoo.medman.com/Peter/Instrument Panel Reference Data.xls"
Windows(wkb).Activate


ActiveSheet.Protect ("MedMan"), DrawingObjects:=True,
Contents:=True, Scenarios:=True
End Sub

Thanks in advance for any help you can provide. I don't relish fixing 100+
cells, especially if I don't know why it blew up in the first place.