?
Solved

VBA/Excel 97 Cell Dependents/Precedents

Posted on 2003-03-25
8
Medium Priority
?
1,068 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
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Currently, there is an issue with being able to copy values from an external application to a dropdown list in Project Web Access (PWA).  The standard copy and paste methods don't seem to work properly. Here is a way to accomplish this task to s…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

569 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