Tom Ogilvy wrote:
I couldn't reproduce any behavior like that in Excel 2003.


OK,

I have some code below to reproduce the problem under Excel 2003.

Copy in this code and run BuildWorkbook to insert some functions. It
will give error message in cells A7 down (never knew it would produce
error on building!). Recalculate function A7 down by pressing F2 and
Ctrl+Shift+Enter. Now double-click in Cell A1.

The reason for using Text property is because the parameters for the
function are all over the workbook and I have linked some cells so that
they can be read in as one range. Unfortunatly when you have a link to
a blank cell Excel gives a 0 as the formula result, which is sometimes a
valid parameter. I have used conditional formatting to blank out this
value as neccessary, and then the text property to access the values.

This is probably not a good solution, so if anyone has a better (or even
different:-)) idea please post.

Thanks

Code in Module 1 is :

Public Function RangeText(R As Range) As String
On Error GoTo Failed
RangeText = R.Text
Exit Function
Failed:
RangeText = Err.Description
End Function

Public Sub BuildWorkbook()
'build worksheet
ActiveSheet.Cells(1, 1).Value = "Double-click this cell to hide"
ActiveSheet.Cells(5, 1).Value = "5"
ActiveSheet.Cells(7, 1).Resize(5, 1).FormulaArray = "=RangeText(A5)"
End Sub


Code in Sheet1 is :

Private unhide_ As Boolean

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Target.Address = ActiveSheet.Cells(1, 1).Address Then

If unhide_ Then
Target.Value = "Double-click this cell to hide"
Else
ActiveSheet.Cells(5, 1).EntireRow.Hidden = True
Target.Value = "Double-click this cell to unhide"
End If

unhide_ = Not unhide_
ActiveSheet.Cells(5, 1).EntireRow.Hidden = unhide_

Cancel = True
End If
End Sub

---
David (posting from a different address)