Ignore Tracing Precedents on xlVeryHidden sheets

adam2079

Board Regular
Joined
May 15, 2008
Messages
51
Hi everyone,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
I have come across a problem in my code for tracing precedents and was hoping someone might be able to help me.<o:p></o:p>
<o:p></o:p>
The problem is when analysing a cell that contains a named range referring to a sheet with a status of xlveryhidden.<o:p></o:p>
<o:p></o:p>
As an example if the following cell contained:<o:p></o:p>
<o:p></o:p>
=CHOOSE(TheCode, D1, D2, D3)<o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
The code is as follows:<o:p></o:p>
<o:p></o:p>
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-com:eek:ffice:smarttags" /><st1:place w:st="on">Loop</st1:place>
rLast.Parent.ClearArrows
Application.Goto rLast
End Sub<o:p></o:p>

<o:p></o:p>
Stepping through the code the loop exits at the following line:<o:p></o:p>
<o:p></o:p>
If rLast.Address(external:=True) = ActiveCell.Address(external:=True) Then Exit Do<o:p></o:p>
<o:p></o:p>
Is there any way of returning the references for the other cells whilst ignoring references to sheets with the status of xlVeryHidden.<o:p></o:p>
<o:p></o:p>
I hope this makes sense but please let me know if you need any other information<o:p></o:p>
<o:p></o:p>
Thanks<o:p></o:p>
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Watch MrExcel Video

Forum statistics

Threads
1,129,804
Messages
5,638,455
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top