?
Solved

VBA/Excel 97 Cell Dependents/Precedents

Posted on 2003-03-25
8
Medium Priority
?
1,052 Views
Last Modified: 2010-05-19
Hi, I'm working on a macro that would graphically mark the cells within a given sheet that have either at least one precedent or at least one dependent cell.

I used the Cells.DirectDependents/DirectPrecedents.Count property to test the condition, but this works
for the active sheet only. Apparently, the Precedents/Dependents located on different sheets than the active one are not included within the Count (though I can invoke them using the Dependents/Precedents
menu bar button).

I replaced the Precedents.Count test with the Cells.HasFormula test, which works fine, but I can't find
any workaround for the Dependents case.

Any ideas would be appreciated.

0
Comment
Question by:jmd7
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 1

Expert Comment

by:tesserhex
ID: 8201969
Keeping it simple, the macro below :

Sub prec_dep()
On Error Resume Next

For r = 1 To 100
For c = 1 To 10
If Cells(r, c).HasFormula = True Then
    Cells(r, c).Select
    Selection.ShowPrecedents
    Selection.ShowDependents
    Cells(r, c).Interior.ColorIndex = 3
End If
Next c
Next r

End Sub


Will loop through cells on the active worksheet in the range r = 1 to 100 and c = 1 to 10 and change the background color of the cells where formulae exist to red as well as including the precedent / dependent arrows.

It could be made as advanced as you like.

Hope this helps get you on the way

J.
0
 

Author Comment

by:jmd7
ID: 8202059
Thanks tesserhex,
though it seems I did not put my point clear enough, sorry.
I think your code tests if each of the cells has a precedent (the HasFormula line) and if so, sets the cell's background to red and displays the dependent/precedent arrows.

What I need to achieve, though, is to set the color of the cell to red also in case the cell has no precedent, but it has at least one dependent - i.e. at least one other cell referres to the tested cell (I don't need to display the arrows at all). I'm afraid your code does not cover this case.

For me it worked with Dependents.Count, but for dependent cells within the SAME SHEET only.

Thanks anyway.

0
 

Author Comment

by:jmd7
ID: 8202095
Thanks tesserhex,
though it seems I did not put my point clear enough, sorry.
I think your code tests if each of the cells has a precedent (the HasFormula line) and if so, sets the cell's background to red and displays the dependent/precedent arrows.

What I need to achieve, though, is to set the color of the cell to red also in case the cell has no precedent, but it has at least one dependent - i.e. at least one other cell referres to the tested cell (I don't need to display the arrows at all). I'm afraid your code does not cover this case.

For me it worked with Dependents.Count, but for dependent cells within the SAME SHEET only.

Thanks anyway.

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 13

Accepted Solution

by:
cri earned 400 total points
ID: 8202233
The .dependents and .precedents work only on the same sheet. By...er...design.

The following code is something I have on the back burner, but it could be useful to start with:


Sub CellsWithExternalDependents()
  'As name says
  'Remarks: Excel's auditing arrows are extremly handy but a bit lame brained:
  '         - Apply for single cell only
  '         - Dependents/Precedents outside active sheet are ignored by VBA
  '         - ShowDependents has a bug in Excel 97
  '         - Quirky NavigateArrow - Columns/rows in activesheet treated as one arrow
  '                                - 'LinkNumber' seems to be a dud parameter
  '
  '         -> A two step approach seems to be necessary
 
  Dim wsSource As Worksheet
  Dim rng2Chk As Range, rngCell As Range, rngDep As Range
  Dim iArrow As Long
  Dim blnWsIsProtected As Boolean
   
  Set wsSource = ActiveSheet
  With wsSource
    'Remember whether worksheet is protected
    blnWsIsProtected = .ProtectContents
    'Unprotect worksheet, if there is a password the standard dialog will run
    .Unprotect
    'If no selection select whole used sheet, otherwise restrict check to selection
    If Selection.Address = ActiveCell.Address Then
      Set rng2Chk = .UsedRange
    Else
      Set rng2Chk = Selection
    End If
    MsgBox rng2Chk.Address
    'Kludgy, but see no other way
    On Error Resume Next
    'Remove all auditing arrows
    .ClearArrows
    For Each rngCell In rng2Chk
      With rngCell
        '.ShowDependents 'does NOT work for Excel 97, work-around below
        .Activate
        Application.ExecuteExcel4Macro "TRACER.DISPLAY(FALSE,TRUE)"
        'Check external references
        iArrow = 0
        Do
          iArrow = iArrow + 1
          .NavigateArrow TowardPrecedent:=False, ArrowNumber:=iArrow
          With ActiveCell
            If .Parent.Name <> wsSource.Name Then
              MsgBox .Parent.Name & .Address & " depends on " & wsSource.Name & rngCell.Address
            End If
          End With
        Loop Until (ActiveCell = rngCell)
      End With
    Next rngCell
   'Re-protect worksheet if applicatble
   'Attention: w/o a password (author's standard, as pw no protection against malicious users)
   If blnWsIsProtected = True Then .Protect
   .ClearArrows
 End With
 'Reset error handler
 On Error GoTo 0
 'Get back where you belong...
  Application.GoTo rng2Chk
End Sub
0
 

Author Comment

by:jmd7
ID: 8202606
Ouch, by design, understood ;-)
The code appears a 'bit' cumbersome, but it DOES work. Thanks for help.
0
 
LVL 13

Expert Comment

by:cri
ID: 8202981
Ouch, cumbersome, understood >8o)

Of course, you can remove the bells and whistles like:
- Switch user selection / used range
- Unprotecting
- Excel 97 bug fix

But you still need a two step approach.
0
 

Author Comment

by:jmd7
ID: 8208790
Yeah, in fact I didn't really mean to comment on the quality of the code as such at all. I just hoped there might be a simple property I missed, instead of having to jump forth and back (which is no way your fault, of course :-))
Thanks a lot again.
0
 
LVL 13

Expert Comment

by:cri
ID: 8212975
I did not take offence. See my profile. And additionally, I am stuck with Excel 97 at work, perhaps the newer versions have improved in this area.

And, I still can not say I use it, seems to be down for the moment, but could be useful for you: www.mgmoreira.com 
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question