Ignore Tracing Precedents on xlVeryHidden sheets

[adam2079]

Hi everyone,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I have come across a problem in my code for tracing precedents and was hoping someone might be able to help me.<o></o>
<o></o>
The problem is when analysing a cell that contains a named range referring to a sheet with a status of xlveryhidden.<o></o>
<o></o>
As an example if the following cell contained:<o></o>
<o></o>
=CHOOSE(TheCode, D1, D2, D3)<o></o>
<o></o>
and TheCode referred to Range A1 of another sheet that had the status of xlVeryHidden then no data would be returned. It does not bother me that no data is returned for TheCode but I would like it to return references of D1, D2 and D3.<o></o>
<o></o>
The code is as follows:<o></o>
<o></o>
Private Sub Process_Precedents()
Dim rLast As Range
Dim iLinkNum As Integer
Dim iArrowNum As Integer
Dim bNewArrow As Boolean

ActiveCell.ShowPrecedents
Set rLast = ActiveCell
iArrowNum = 1
iLinkNum = 1
bNewArrow = True
TheCounter = 9
Do
Do
Application.Goto rLast
On Error Resume Next
ActiveCell.NavigateArrow towardprecedent:=True, arrownumber:=iArrowNum, linknumber:=iLinkNum
If Err.Number > 0 Then Exit Do
On Error GoTo 0
If rLast.Address(external:=True) = ActiveCell.Address(external:=True) Then Exit Do
bNewArrow = False
If rLast.Worksheet.Parent.Name = ActiveCell.Worksheet.Parent.Name Then
'Local
ThisWorkbook.Sheets("Sheet1").Cells(TheCounter, TheColumn) = Workbooks("" & FileToOpen & "").ActiveSheet.Name & "!" & Selection.Address
TheCounter = TheCounter + 1
Else
'External
ThisWorkbook.Sheets("Sheet1").Cells(TheCounter, TheColumn) = Workbooks("" & FileToOpen & "").ActiveSheet.Name & "!" & Selection.Address(external:=True)
TheCounter = TheCounter + 1
End If
iLinkNum = iLinkNum + 1
Loop
If bNewArrow Then Exit Do
iLinkNum = 1
bNewArrow = True
iArrowNum = iArrowNum + 1
<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1lace w:st="on">Loop</st1lace>
rLast.Parent.ClearArrows
Application.Goto rLast
End Sub<o></o>
<o></o>
Stepping through the code the loop exits at the following line:<o></o>
<o></o>
If rLast.Address(external:=True) = ActiveCell.Address(external:=True) Then Exit Do<o></o>
<o></o>
Is there any way of returning the references for the other cells whilst ignoring references to sheets with the status of xlVeryHidden.<o></o>
<o></o>
I hope this makes sense but please let me know if you need any other information<o></o>
<o></o>
Thanks<o></o>

 

[adam2079]

I've managed to resolve the issue using ASAP utilities

Thanks