Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Thanked 0 Times in 0 Posts

    Tracing Dependents to other Worksheets (XL2000 SR1)


    I am trying to develop a utility that can trace dependents and precedents to other sheets in the workbook.

    The code that I am using to list the precedents or dependents is :

    For n = 1 To ActiveCell.Precedents.Count
    UserForm1.ListBox1.AddItem ActiveCell.Precedents(n).AddressLocal
    Next n

    This code lists all cells that are contained on the same sheet as the formula, but if the formula includes a cell from another sheet, it does not show up.

    Is there away to capture all of the cell address for every cell that impacts or is impacted by another cell?


  2. #2
    Super Moderator macropod's Avatar
    Join Date
    May 2002
    Canberra, Australian Capital Territory, Australia
    Thanked 465 Times in 382 Posts

    Re: Tracing Dependents to other Worksheets (XL2000 SR1)

    You can't do this with the precedents property (which returns an absolute reference even if the underlying formula uses a relative reference). Even
    MsgBox ActiveCell.Precedents.Count
    only counts precedents on the same worksheet.

    To overcome this, you could parse the formula itself, extracting the references to other cells/ranges/worksheets/workbooks. You might also want to list repeated references only once. Consider too how you want to represent named ranges - 'precedents' returns the range, not its name.

    Hope this helps.

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts