• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 488
  • Last Modified:

Excel .navigatearrow and hidden sheets

Hi everyone,
I have read many topics about .navigatearrow and seen many functions that largely do the same thing.  However, it seems that none of these functions will return an offsheet dependent if the sheet upon which the dependent cell lives, is hidden.

Does anyone know a way around this?

What I want to do is "simply" hah represent the list of links that is provided in the arrow box on the worksheet when you trace dependents in a Userform.  It appears that the only way to get this is to .navigatearrow through all the links and capturethe sheet and address when the cell gets selected.  But if the sheet is hidden, the .navigatearrow won't select it.

Kind regards,
Robert
0
RODELS
Asked:
RODELS
1 Solution
 
Arno KosterCommented:
Excel does not allow tracing of dependancies along hidden sheets.
Even the range.dependents object in VBA macro code does not contain references to hidden cells.

you could make a macro for this that freezes the screen, unhides all worksheets, makes the list, hides all appropriate worksheets again and finally unfreezes the screen.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now