I have a large and complicated excel file with a lot of named cell ranges.
Every time the user makes a change, a macro updates every named range,
changing data validation as necessary, changing fonts to signal errors, and
in some cases entering new excel formulas. As long as I work on this file
stand-alone, everything works.

The problem is when I open another file with cell formulas that refer to
this file. As long as that second file is open, the macro in my origonal
file crashes. It seems to crash at the point where it changes any cell
formula. I'm not certain, because it doesn't go into debug mode, but rather
simply stops. When I try to trap the error, I read it as error 1004 -
"Application Defined or Object Defined Error"

I have not found any way to make this problem go away, other than closing
the file that links to this one. Anyone know what is happening here?

Possibly the other workbook has UDF's that are improperly constructed and
raise an error.

If you don't get an error, it is unclear how you trap one. Maybe your
current code has On Error Resume Next that is masking some errors in your
code as well.

--
Regards,
Tom Ogilvy


"Dr Rubick" <Dr wrote in message
...
I have a large and complicated excel file with a lot of named cell ranges.
Every time the user makes a change, a macro updates every named range,
changing data validation as necessary, changing fonts to signal errors,
and
in some cases entering new excel formulas. As long as I work on this file
stand-alone, everything works.

The problem is when I open another file with cell formulas that refer to
this file. As long as that second file is open, the macro in my origonal
file crashes. It seems to crash at the point where it changes any cell
formula. I'm not certain, because it doesn't go into debug mode, but
rather
simply stops. When I try to trap the error, I read it as error 1004 -
"Application Defined or Object Defined Error"

I have not found any way to make this problem go away, other than closing
the file that links to this one. Anyone know what is happening here?

I guess I wasn't clear on that in the origonal message. At first it just
stopped in the middle of running the code without displaying any error
message or brining up the visual basic editor. After getting this error, I
added an "On Error Goto..." to find out what the error is. That's where I
got error number 1004.

I don't recognize your "UDF" acronym. What are they and how would I look
for them?

"Tom Ogilvy" wrote:

Possibly the other workbook has UDF's that are improperly constructed and
raise an error.

If you don't get an error, it is unclear how you trap one. Maybe your
current code has On Error Resume Next that is masking some errors in your
code as well.

--
Regards,
Tom Ogilvy


"Dr Rubick" <Dr wrote in message
...
I have a large and complicated excel file with a lot of named cell ranges.
Every time the user makes a change, a macro updates every named range,
changing data validation as necessary, changing fonts to signal errors,
and
in some cases entering new excel formulas. As long as I work on this file
stand-alone, everything works.

The problem is when I open another file with cell formulas that refer to
this file. As long as that second file is open, the macro in my origonal
file crashes. It seems to crash at the point where it changes any cell
formula. I'm not certain, because it doesn't go into debug mode, but
rather
simply stops. When I try to trap the error, I read it as error 1004 -
"Application Defined or Object Defined Error"

I have not found any way to make this problem go away, other than closing
the file that links to this one. Anyone know what is happening here?

Can you post the procedure where the error is being raised. Tough to debug
without some code... UDF normally stands for User Defined Function... That is
probably what Tom means.

"Dr Rubick" wrote:

I guess I wasn't clear on that in the origonal message. At first it just
stopped in the middle of running the code without displaying any error
message or brining up the visual basic editor. After getting this error, I
added an "On Error Goto..." to find out what the error is. That's where I
got error number 1004.

I don't recognize your "UDF" acronym. What are they and how would I look
for them?

"Tom Ogilvy" wrote:

Possibly the other workbook has UDF's that are improperly constructed and
raise an error.

If you don't get an error, it is unclear how you trap one. Maybe your
current code has On Error Resume Next that is masking some errors in your
code as well.

--
Regards,
Tom Ogilvy


"Dr Rubick" <Dr wrote in message
...
I have a large and complicated excel file with a lot of named cell ranges.
Every time the user makes a change, a macro updates every named range,
changing data validation as necessary, changing fonts to signal errors,
and
in some cases entering new excel formulas. As long as I work on this file
stand-alone, everything works.

The problem is when I open another file with cell formulas that refer to
this file. As long as that second file is open, the macro in my origonal
file crashes. It seems to crash at the point where it changes any cell
formula. I'm not certain, because it doesn't go into debug mode, but
rather
simply stops. When I try to trap the error, I read it as error 1004 -
"Application Defined or Object Defined Error"

I have not found any way to make this problem go away, other than closing
the file that links to this one. Anyone know what is happening here?

Below is the first part of the subroutine that crashes. I added the "-"
label to the lines that seem to crash. I say that because when I comment out
those lines, the code gets past this loop.

For Each Nm In ThisWorkbook.Names
Nm_Name = Nm.Name
If (Len(Nm_Name) 5) And (Nm_Name < "Prog_Subprog__sel") And
(Nm_Name < "Goto_Error_Hyperlink") Then
If Right(Nm_Name, 5) = "__sel" Then
Set rng = Nm.RefersToRange
Set RawRng = ThisWorkbook.Worksheets("Raw
Data").Cells(ThisWorkbook.Names(Left(Nm_Name, _

Len(Nm_Name) - 3) & "row").RefersToRange.Row, SelCol)
MyFormula = RawRng.Address(RowAbsolute:=True,
ColumnAbsolute:=True, ReferenceStyle:=xlA1, external:=True)
If (InStr(MyFormula, "[") < 0) And (InStr(MyFormula, "]")
InStr(MyFormula, "[")) Then
MyFormula = Left(MyFormula, InStr(MyFormula, "[") - 1) &
Mid(MyFormula, InStr(MyFormula, "]") + 1)
End If
MyFormula = "=IF(" & MyFormula & "=" & Chr(34) & Chr(34) &
"," & Chr(34) & Chr(34) & "," & MyFormula & ")"
If IsError(rng.Value) Or Not rng.HasFormula Then
EnteredValue = rng.Value
If IsError(EnteredValue) Then EnteredValue = ""
- rng.Formula = MyFormula
If ProgSelIsBlank Then
- rng.Value = RawRng.Value
Else
- RawRng.Value = EnteredValue
End If
End If
If rng.Formula < MyFormula Then
- rng.Formula = MyFormula
End If
End If
End If
Next Nm

The other workbook only has one user-defined subroutine, and it makes no
difference when I comment it out completely. But since it isn't that long,
I'll include it here for the sake of completeness:

Sub Update_Att_List()
Dim rw As Integer
Dim inRw As Integer
Dim Col As Integer
Dim wkb As Workbook
Dim sht As Worksheet
Dim Nm As Name

For rw = 1 To Me.UsedRange.Rows.Count
If Me.Cells(rw, 1).Value = "Attribute Value Links:" Then Exit For
Next rw
If Me.Cells(rw, 1).Value < "Attribute Value Links:" Then Stop
rw = rw + 1
Me.Range(Me.Cells(rw, 1), Me.Cells(Me.UsedRange.Rows.Count,
1)).EntireRow.Delete
For Each Nm In ThisWorkbook.Names
Nm.Delete
Next Nm
For Each wkb In Application.Workbooks
If wkb.FullName = Me.Range("B2").Value Then Exit For
Next wkb
If wkb.FullName < Me.Range("B2").Value Then Stop ' Open the source
workbook
Set sht = wkb.Sheets("Raw Data")
For Col = 1 To sht.UsedRange.Columns.Count
If sht.Cells(1, Col).Value = "Attribute" Then Exit For
Next Col
If sht.Cells(1, Col).Value < "Attribute" Then Stop
inRw = 2
Do Until (sht.Cells(inRw, Col).Value = "") Or (sht.Cells(inRw, 1).Value
= "CAL1_x")
Me.Cells(rw, 1).Value = sht.Cells(inRw, Col).Value
Me.Cells(rw, 1).HorizontalAlignment = xlRight
Me.Cells(rw, 2).Formula = "=OFFSET(INDIRECT(" & Chr(34) & "'" &
Chr(34) & "&$B$2&" & Chr(34) & "'!" & sht.Cells(inRw, Col).Value & "__row" &
Chr(34) & "),0,$B$6)"
ThisWorkbook.Names.Add Name:=sht.Cells(inRw, Col).Value,
RefersTo:=Me.Cells(rw, 2)
rw = rw + 1
inRw = inRw + 1
Loop
End Sub


"Jim Thomlinson" wrote:

Can you post the procedure where the error is being raised. Tough to debug
without some code... UDF normally stands for User Defined Function... That is
probably what Tom means.

"Dr Rubick" wrote:

I guess I wasn't clear on that in the origonal message. At first it just
stopped in the middle of running the code without displaying any error
message or brining up the visual basic editor. After getting this error, I
added an "On Error Goto..." to find out what the error is. That's where I
got error number 1004.

I don't recognize your "UDF" acronym. What are they and how would I look
for them?

"Tom Ogilvy" wrote:

Possibly the other workbook has UDF's that are improperly constructed and
raise an error.

If you don't get an error, it is unclear how you trap one. Maybe your
current code has On Error Resume Next that is masking some errors in your
code as well.

--
Regards,
Tom Ogilvy


"Dr Rubick" <Dr wrote in message
...
I have a large and complicated excel file with a lot of named cell ranges.
Every time the user makes a change, a macro updates every named range,
changing data validation as necessary, changing fonts to signal errors,
and
in some cases entering new excel formulas. As long as I work on this file
stand-alone, everything works.

The problem is when I open another file with cell formulas that refer to
this file. As long as that second file is open, the macro in my origonal
file crashes. It seems to crash at the point where it changes any cell
formula. I'm not certain, because it doesn't go into debug mode, but
rather
simply stops. When I try to trap the error, I read it as error 1004 -
"Application Defined or Object Defined Error"

I have not found any way to make this problem go away, other than closing
the file that links to this one. Anyone know what is happening here?

A few lines up from your problem you set rng and RawRng. Have you tried
placing a break point on those lines and watch on those two items to see if
they exist and if so what they look like when they are run stand alone as
opposed to what they look like when another linked spreadsheet is open. That
is where I would start.

HTH

"Dr Rubick" wrote:

Below is the first part of the subroutine that crashes. I added the "-"
label to the lines that seem to crash. I say that because when I comment out
those lines, the code gets past this loop.

For Each Nm In ThisWorkbook.Names
Nm_Name = Nm.Name
If (Len(Nm_Name) 5) And (Nm_Name < "Prog_Subprog__sel") And
(Nm_Name < "Goto_Error_Hyperlink") Then
If Right(Nm_Name, 5) = "__sel" Then
Set rng = Nm.RefersToRange
Set RawRng = ThisWorkbook.Worksheets("Raw
Data").Cells(ThisWorkbook.Names(Left(Nm_Name, _

Len(Nm_Name) - 3) & "row").RefersToRange.Row, SelCol)
MyFormula = RawRng.Address(RowAbsolute:=True,
ColumnAbsolute:=True, ReferenceStyle:=xlA1, external:=True)
If (InStr(MyFormula, "[") < 0) And (InStr(MyFormula, "]")
InStr(MyFormula, "[")) Then
MyFormula = Left(MyFormula, InStr(MyFormula, "[") - 1) &
Mid(MyFormula, InStr(MyFormula, "]") + 1)
End If
MyFormula = "=IF(" & MyFormula & "=" & Chr(34) & Chr(34) &
"," & Chr(34) & Chr(34) & "," & MyFormula & ")"
If IsError(rng.Value) Or Not rng.HasFormula Then
EnteredValue = rng.Value
If IsError(EnteredValue) Then EnteredValue = ""
- rng.Formula = MyFormula
If ProgSelIsBlank Then
- rng.Value = RawRng.Value
Else
- RawRng.Value = EnteredValue
End If
End If
If rng.Formula < MyFormula Then
- rng.Formula = MyFormula
End If
End If
End If
Next Nm

The other workbook only has one user-defined subroutine, and it makes no
difference when I comment it out completely. But since it isn't that long,
I'll include it here for the sake of completeness:

Sub Update_Att_List()
Dim rw As Integer
Dim inRw As Integer
Dim Col As Integer
Dim wkb As Workbook
Dim sht As Worksheet
Dim Nm As Name

For rw = 1 To Me.UsedRange.Rows.Count
If Me.Cells(rw, 1).Value = "Attribute Value Links:" Then Exit For
Next rw
If Me.Cells(rw, 1).Value < "Attribute Value Links:" Then Stop
rw = rw + 1
Me.Range(Me.Cells(rw, 1), Me.Cells(Me.UsedRange.Rows.Count,
1)).EntireRow.Delete
For Each Nm In ThisWorkbook.Names
Nm.Delete
Next Nm
For Each wkb In Application.Workbooks
If wkb.FullName = Me.Range("B2").Value Then Exit For
Next wkb
If wkb.FullName < Me.Range("B2").Value Then Stop ' Open the source
workbook
Set sht = wkb.Sheets("Raw Data")
For Col = 1 To sht.UsedRange.Columns.Count
If sht.Cells(1, Col).Value = "Attribute" Then Exit For
Next Col
If sht.Cells(1, Col).Value < "Attribute" Then Stop
inRw = 2
Do Until (sht.Cells(inRw, Col).Value = "") Or (sht.Cells(inRw, 1).Value
= "CAL1_x")
Me.Cells(rw, 1).Value = sht.Cells(inRw, Col).Value
Me.Cells(rw, 1).HorizontalAlignment = xlRight
Me.Cells(rw, 2).Formula = "=OFFSET(INDIRECT(" & Chr(34) & "'" &
Chr(34) & "&$B$2&" & Chr(34) & "'!" & sht.Cells(inRw, Col).Value & "__row" &
Chr(34) & "),0,$B$6)"
ThisWorkbook.Names.Add Name:=sht.Cells(inRw, Col).Value,
RefersTo:=Me.Cells(rw, 2)
rw = rw + 1
inRw = inRw + 1
Loop
End Sub


"Jim Thomlinson" wrote:

Can you post the procedure where the error is being raised. Tough to debug
without some code... UDF normally stands for User Defined Function... That is
probably what Tom means.

"Dr Rubick" wrote:

I guess I wasn't clear on that in the origonal message. At first it just
stopped in the middle of running the code without displaying any error
message or brining up the visual basic editor. After getting this error, I
added an "On Error Goto..." to find out what the error is. That's where I
got error number 1004.

I don't recognize your "UDF" acronym. What are they and how would I look
for them?

"Tom Ogilvy" wrote:

Possibly the other workbook has UDF's that are improperly constructed and
raise an error.

If you don't get an error, it is unclear how you trap one. Maybe your
current code has On Error Resume Next that is masking some errors in your
code as well.

--
Regards,
Tom Ogilvy


"Dr Rubick" <Dr wrote in message
...
I have a large and complicated excel file with a lot of named cell ranges.
Every time the user makes a change, a macro updates every named range,
changing data validation as necessary, changing fonts to signal errors,
and
in some cases entering new excel formulas. As long as I work on this file
stand-alone, everything works.

The problem is when I open another file with cell formulas that refer to
this file. As long as that second file is open, the macro in my origonal
file crashes. It seems to crash at the point where it changes any cell
formula. I'm not certain, because it doesn't go into debug mode, but
rather
simply stops. When I try to trap the error, I read it as error 1004 -
"Application Defined or Object Defined Error"

I have not found any way to make this problem go away, other than closing
the file that links to this one. Anyone know what is happening here?

I tried this, but I couldn't find any differences in rng, RawRng, or any
other variables that seemed relevant. I've been trying a few other things,
and I made one interesting discovery:

When I turn calculation to "Manual" before running the macro, it doesn't
crash and everything works. The problem only occurs if calculation is set to
"Automatic" before the macro runs. I know how to turn off automatic
calculation in a macro, and I already had that in this macro to increase
speed.

Now here's the real mystery: when the linked sheet is open and calculation
is set to "Automatic" before runnig the macro, it seems to ignore the
"Application.Calculation = xlCalculationManual" command. I've even moved
Application.Calculation to the watch window, but it will not let me change
that value while the macro is running. Any ideas as to why, and what else I
can do to programmatically turn off auto-calculation in this condition?

"Jim Thomlinson" wrote:

A few lines up from your problem you set rng and RawRng. Have you tried
placing a break point on those lines and watch on those two items to see if
they exist and if so what they look like when they are run stand alone as
opposed to what they look like when another linked spreadsheet is open. That
is where I would start.

HTH

"Dr Rubick" wrote:

Below is the first part of the subroutine that crashes. I added the "-"
label to the lines that seem to crash. I say that because when I comment out
those lines, the code gets past this loop.

For Each Nm In ThisWorkbook.Names
Nm_Name = Nm.Name
If (Len(Nm_Name) 5) And (Nm_Name < "Prog_Subprog__sel") And
(Nm_Name < "Goto_Error_Hyperlink") Then
If Right(Nm_Name, 5) = "__sel" Then
Set rng = Nm.RefersToRange
Set RawRng = ThisWorkbook.Worksheets("Raw
Data").Cells(ThisWorkbook.Names(Left(Nm_Name, _

Len(Nm_Name) - 3) & "row").RefersToRange.Row, SelCol)
MyFormula = RawRng.Address(RowAbsolute:=True,
ColumnAbsolute:=True, ReferenceStyle:=xlA1, external:=True)
If (InStr(MyFormula, "[") < 0) And (InStr(MyFormula, "]")
InStr(MyFormula, "[")) Then
MyFormula = Left(MyFormula, InStr(MyFormula, "[") - 1) &
Mid(MyFormula, InStr(MyFormula, "]") + 1)
End If
MyFormula = "=IF(" & MyFormula & "=" & Chr(34) & Chr(34) &
"," & Chr(34) & Chr(34) & "," & MyFormula & ")"
If IsError(rng.Value) Or Not rng.HasFormula Then
EnteredValue = rng.Value
If IsError(EnteredValue) Then EnteredValue = ""
- rng.Formula = MyFormula
If ProgSelIsBlank Then
- rng.Value = RawRng.Value
Else
- RawRng.Value = EnteredValue
End If
End If
If rng.Formula < MyFormula Then
- rng.Formula = MyFormula
End If
End If
End If
Next Nm

The other workbook only has one user-defined subroutine, and it makes no
difference when I comment it out completely. But since it isn't that long,
I'll include it here for the sake of completeness:

Sub Update_Att_List()
Dim rw As Integer
Dim inRw As Integer
Dim Col As Integer
Dim wkb As Workbook
Dim sht As Worksheet
Dim Nm As Name

For rw = 1 To Me.UsedRange.Rows.Count
If Me.Cells(rw, 1).Value = "Attribute Value Links:" Then Exit For
Next rw
If Me.Cells(rw, 1).Value < "Attribute Value Links:" Then Stop
rw = rw + 1
Me.Range(Me.Cells(rw, 1), Me.Cells(Me.UsedRange.Rows.Count,
1)).EntireRow.Delete
For Each Nm In ThisWorkbook.Names
Nm.Delete
Next Nm
For Each wkb In Application.Workbooks
If wkb.FullName = Me.Range("B2").Value Then Exit For
Next wkb
If wkb.FullName < Me.Range("B2").Value Then Stop ' Open the source
workbook
Set sht = wkb.Sheets("Raw Data")
For Col = 1 To sht.UsedRange.Columns.Count
If sht.Cells(1, Col).Value = "Attribute" Then Exit For
Next Col
If sht.Cells(1, Col).Value < "Attribute" Then Stop
inRw = 2
Do Until (sht.Cells(inRw, Col).Value = "") Or (sht.Cells(inRw, 1).Value
= "CAL1_x")
Me.Cells(rw, 1).Value = sht.Cells(inRw, Col).Value
Me.Cells(rw, 1).HorizontalAlignment = xlRight
Me.Cells(rw, 2).Formula = "=OFFSET(INDIRECT(" & Chr(34) & "'" &
Chr(34) & "&$B$2&" & Chr(34) & "'!" & sht.Cells(inRw, Col).Value & "__row" &
Chr(34) & "),0,$B$6)"
ThisWorkbook.Names.Add Name:=sht.Cells(inRw, Col).Value,
RefersTo:=Me.Cells(rw, 2)
rw = rw + 1
inRw = inRw + 1
Loop
End Sub


"Jim Thomlinson" wrote:

Can you post the procedure where the error is being raised. Tough to debug
without some code... UDF normally stands for User Defined Function... That is
probably what Tom means.

"Dr Rubick" wrote:

I guess I wasn't clear on that in the origonal message. At first it just
stopped in the middle of running the code without displaying any error
message or brining up the visual basic editor. After getting this error, I
added an "On Error Goto..." to find out what the error is. That's where I
got error number 1004.

I don't recognize your "UDF" acronym. What are they and how would I look
for them?

"Tom Ogilvy" wrote:

Possibly the other workbook has UDF's that are improperly constructed and
raise an error.

If you don't get an error, it is unclear how you trap one. Maybe your
current code has On Error Resume Next that is masking some errors in your
code as well.

--
Regards,
Tom Ogilvy


"Dr Rubick" <Dr wrote in message
...
I have a large and complicated excel file with a lot of named cell ranges.
Every time the user makes a change, a macro updates every named range,
changing data validation as necessary, changing fonts to signal errors,
and
in some cases entering new excel formulas. As long as I work on this file
stand-alone, everything works.

The problem is when I open another file with cell formulas that refer to
this file. As long as that second file is open, the macro in my origonal
file crashes. It seems to crash at the point where it changes any cell
formula. I'm not certain, because it doesn't go into debug mode, but
rather
simply stops. When I try to trap the error, I read it as error 1004 -
"Application Defined or Object Defined Error"

I have not found any way to make this problem go away, other than closing
the file that links to this one. Anyone know what is happening here?

I've worked further on this, and I think I'm closer to the root problem.
Since this post is carrying so much text already, I'll describe where I am
now in a new posting.

"Dr Rubick" wrote:

I tried this, but I couldn't find any differences in rng, RawRng, or any
other variables that seemed relevant. I've been trying a few other things,
and I made one interesting discovery:

When I turn calculation to "Manual" before running the macro, it doesn't
crash and everything works. The problem only occurs if calculation is set to
"Automatic" before the macro runs. I know how to turn off automatic
calculation in a macro, and I already had that in this macro to increase
speed.

Now here's the real mystery: when the linked sheet is open and calculation
is set to "Automatic" before runnig the macro, it seems to ignore the
"Application.Calculation = xlCalculationManual" command. I've even moved
Application.Calculation to the watch window, but it will not let me change
that value while the macro is running. Any ideas as to why, and what else I
can do to programmatically turn off auto-calculation in this condition?

"Jim Thomlinson" wrote:

A few lines up from your problem you set rng and RawRng. Have you tried
placing a break point on those lines and watch on those two items to see if
they exist and if so what they look like when they are run stand alone as
opposed to what they look like when another linked spreadsheet is open. That
is where I would start.

HTH

"Dr Rubick" wrote:

Below is the first part of the subroutine that crashes. I added the "-"
label to the lines that seem to crash. I say that because when I comment out
those lines, the code gets past this loop.

For Each Nm In ThisWorkbook.Names
Nm_Name = Nm.Name
If (Len(Nm_Name) 5) And (Nm_Name < "Prog_Subprog__sel") And
(Nm_Name < "Goto_Error_Hyperlink") Then
If Right(Nm_Name, 5) = "__sel" Then
Set rng = Nm.RefersToRange
Set RawRng = ThisWorkbook.Worksheets("Raw
Data").Cells(ThisWorkbook.Names(Left(Nm_Name, _

Len(Nm_Name) - 3) & "row").RefersToRange.Row, SelCol)
MyFormula = RawRng.Address(RowAbsolute:=True,
ColumnAbsolute:=True, ReferenceStyle:=xlA1, external:=True)
If (InStr(MyFormula, "[") < 0) And (InStr(MyFormula, "]")
InStr(MyFormula, "[")) Then
MyFormula = Left(MyFormula, InStr(MyFormula, "[") - 1) &
Mid(MyFormula, InStr(MyFormula, "]") + 1)
End If
MyFormula = "=IF(" & MyFormula & "=" & Chr(34) & Chr(34) &
"," & Chr(34) & Chr(34) & "," & MyFormula & ")"
If IsError(rng.Value) Or Not rng.HasFormula Then
EnteredValue = rng.Value
If IsError(EnteredValue) Then EnteredValue = ""
- rng.Formula = MyFormula
If ProgSelIsBlank Then
- rng.Value = RawRng.Value
Else
- RawRng.Value = EnteredValue
End If
End If
If rng.Formula < MyFormula Then
- rng.Formula = MyFormula
End If
End If
End If
Next Nm

The other workbook only has one user-defined subroutine, and it makes no
difference when I comment it out completely. But since it isn't that long,
I'll include it here for the sake of completeness:

Sub Update_Att_List()
Dim rw As Integer
Dim inRw As Integer
Dim Col As Integer
Dim wkb As Workbook
Dim sht As Worksheet
Dim Nm As Name

For rw = 1 To Me.UsedRange.Rows.Count
If Me.Cells(rw, 1).Value = "Attribute Value Links:" Then Exit For
Next rw
If Me.Cells(rw, 1).Value < "Attribute Value Links:" Then Stop
rw = rw + 1
Me.Range(Me.Cells(rw, 1), Me.Cells(Me.UsedRange.Rows.Count,
1)).EntireRow.Delete
For Each Nm In ThisWorkbook.Names
Nm.Delete
Next Nm
For Each wkb In Application.Workbooks
If wkb.FullName = Me.Range("B2").Value Then Exit For
Next wkb
If wkb.FullName < Me.Range("B2").Value Then Stop ' Open the source
workbook
Set sht = wkb.Sheets("Raw Data")
For Col = 1 To sht.UsedRange.Columns.Count
If sht.Cells(1, Col).Value = "Attribute" Then Exit For
Next Col
If sht.Cells(1, Col).Value < "Attribute" Then Stop
inRw = 2
Do Until (sht.Cells(inRw, Col).Value = "") Or (sht.Cells(inRw, 1).Value
= "CAL1_x")
Me.Cells(rw, 1).Value = sht.Cells(inRw, Col).Value
Me.Cells(rw, 1).HorizontalAlignment = xlRight
Me.Cells(rw, 2).Formula = "=OFFSET(INDIRECT(" & Chr(34) & "'" &
Chr(34) & "&$B$2&" & Chr(34) & "'!" & sht.Cells(inRw, Col).Value & "__row" &
Chr(34) & "),0,$B$6)"
ThisWorkbook.Names.Add Name:=sht.Cells(inRw, Col).Value,
RefersTo:=Me.Cells(rw, 2)
rw = rw + 1
inRw = inRw + 1
Loop
End Sub


"Jim Thomlinson" wrote:

Can you post the procedure where the error is being raised. Tough to debug
without some code... UDF normally stands for User Defined Function... That is
probably what Tom means.

"Dr Rubick" wrote:

I guess I wasn't clear on that in the origonal message. At first it just
stopped in the middle of running the code without displaying any error
message or brining up the visual basic editor. After getting this error, I
added an "On Error Goto..." to find out what the error is. That's where I
got error number 1004.

I don't recognize your "UDF" acronym. What are they and how would I look
for them?

"Tom Ogilvy" wrote:

Possibly the other workbook has UDF's that are improperly constructed and
raise an error.

If you don't get an error, it is unclear how you trap one. Maybe your
current code has On Error Resume Next that is masking some errors in your
code as well.

--
Regards,
Tom Ogilvy


"Dr Rubick" <Dr wrote in message
...
I have a large and complicated excel file with a lot of named cell ranges.
Every time the user makes a change, a macro updates every named range,
changing data validation as necessary, changing fonts to signal errors,
and
in some cases entering new excel formulas. As long as I work on this file
stand-alone, everything works.

The problem is when I open another file with cell formulas that refer to
this file. As long as that second file is open, the macro in my origonal
file crashes. It seems to crash at the point where it changes any cell
formula. I'm not certain, because it doesn't go into debug mode, but
rather
simply stops. When I try to trap the error, I read it as error 1004 -
"Application Defined or Object Defined Error"

I have not found any way to make this problem go away, other than closing
the file that links to this one. Anyone know what is happening here?