Excel Macro -- Group Sheets and Trace Dependents?

2 questions rolled into one...

1) Can not yet see a direct solution for grouping worksheets. Some work-arounds:

a) Right click on the left bottom sheet 'spinners' (i.e. |< < > >| . Does the pop-up list help the navigation ?

b) Define a worksheet with hyperlinks to your sheets and use the group/ungroup feature to filter them. If you constantly expand the workbook, keeping the sheets and the list in sync will be bothersome.

2) Only with a macro. Ever done something in VBA ?

Thanks for the comments cri.  

1.  The feature to group sheets would still be very helpful in organizing the model.  I am aware of the spinner navigation.  I have done the hyperlinking method, but grouping will still be a good addition to help navigation.  Any ideas on how a grouping feature could be built?

2.  I am not familiar w/ VBA at all.  Any help or guidance is appreciated.

Thanks for the comments cri.  

1.  The feature to group sheets would still be very helpful in organizing the model.  I am aware of the spinner navigation.  I have done the hyperlinking method, but grouping will still be a good addition to help navigation.  Any ideas on how a grouping feature could be built?

2.  I am not familiar w/ VBA at all.  Any help or guidance is appreciated.

vik1109, there might be a misunderstanding. I was refering to group/ungroup the _hyperlinks_, not the  worksheets. What could be done is to hide the worksheets according whether they are visible, but this is hardly what you are after.

Regarding VBA: I could give you tons of links, but IMO the best way to start is to record a macro, compact it (i.e. almost always 'Select' is superfluous) and add  whistles and bells. A macro to determine void precedents however is a bit steep to start with. Will see what I can come up with, but I do not promise anything.  

BTW: You will attract more experts if you assign points according the difficulty. I do (almost) not mind as long as I meet the quota to stay in KPro, which is already the case, but others might.

cri, i appreciate your help.  I would add more points, however, I just joined and am not able to add more points at this time.  I know this is not an easy task.

Thanks.

Several questions:

a) Do you want to check a whole worksheet or a selected range ? Is the 'Null Precedent' check all you want ? I have a macro which sort of applies the trace dependents/precedents for a whole range, but, depending on the complexity of the workbook and the choosen tracing depth you get a blue screen <g>.

b) Do you already have some macros or functions which you would like to use across workbooks ?

c) Do you have _any_ programming experience ?

A.  checking a whole worksheet would be good.  And then have some sort of ouput telling which null cells are precedents.  

B.  I dont' have any other macros or functions that I want to use across workbooks besides this one.

C.  I have very little programming experience.

Ok, will see, but not before in a week time.

In the meantime, follow https://www.experts-exchange.com/questions/20400353/Tracing-Excel-Worksheet.html . If you post a cross reference to this one or a comment (can be 'listening') you will be able to access the answer w/o paying 10%

Here two alternatives. If you can exclude for sure links to other worksheets, take #1, else #2 is the better solution.

a) In Excel: Tools|Macros|VisualBasicEditor (or Alt+F11)
b) In VBE: In View|ProjectWindows: Highlight your workbook -> Insert|Module -> paste the following Code


Option Explicit  'to prevent erros arising from typos
Option Compare Text 'in order to make string comparisions case insensitive

Sub VoidCheck1()
  'Check whether a formula is dependent from an empty cell
  'Attention: Only within the _active_ worksheet !
 
  Dim rngCell As Range
  Dim WsIsProtected As Boolean
   
  'Attention: If a password is required a prompt will issued automatically,
  '           the macro will run, BUT will not re-apply the _password_.
  With ActiveSheet
    'Remember whether worksheet is protected
    WsIsProtected = .ProtectContents
    'Unprotect worksheet, if there is a password the standard dialog will run
    .Unprotect
    'Remove all auditing arrows
    .ClearArrows
    For Each rngCell In .UsedRange
      'Check all cells which are empty or contain spaces
      If IsEmpty(rngCell.Value) Or Trim(rngCell.Text) = "" Then
        rngCell.Activate
        'Work around as "rngCell.ShowDependents" does not work, it is a bug in 97
        Application.ExecuteExcel4Macro "TRACER.DISPLAY(FALSE,TRUE)"
      End If
    Next rngCell
    If WsIsProtected = True Then .Protect
  End With
End Sub


Sub VoidCheck2()
  'Displays _all_ precedents of a cell if at least one is empty or contains only spaces
 
  Dim rngCell As Range, rngCellPrec As Range
  Dim WsIsProtected As Boolean
   
  'Attention: If a password is required a prompt will issued automatically,
  '           the macro will run, BUT it will not re-apply the _password_.
  With ActiveSheet
    'Remember whether worksheet is protected
    WsIsProtected = .ProtectContents
    'Unprotect worksheet, if there is a password the standard dialog will run
    .Unprotect
    'Remove all auditing arrows
    .ClearArrows
    On Error Resume Next
    For Each rngCell In .UsedRange
      'Check whether preceding cells are empty or contain spaces
      For Each rngCellPrec In rngCell.Precedents
        If IsEmpty(rngCellPrec.Value) Or Trim(rngCellPrec.Value) = "" Then
          rngCell.ShowPrecedents
          Exit For
        End If
      Next rngCellPrec
    Next rngCell
    On Error GoTo 0
    If WsIsProtected = True Then .Protect
  End With
End Sub



c) Save and return to workbook (view)

d) Tools|Macros: Select Macro and click 'Run'.

e) If you like the functionality and if you use it alot, ask back regarding how to make the macro easier/faster to start. If not: Alter/Update the code, best way to learn VBA. Case it does not run anymore: Paste code here.

cri..the macro works pretty well, however there is still one flaw in it. here's an example:

on sheet 1 enter the following date:

in column A number the cells 1 to 10 for the first 10 rows.

then assign 3 different cells the following formulas:

=A1*B5

=Sheet2!B5

=A1*Sheet2!B5

the macro will catch the first two examples as having a null precedents.  However, it won't catch the second formula despite Sheet2!B5 being a null cell.  I am referring to the 2nd macro you suggested where it links to other sheets.  

Can this be fixed?

Strange, it works on my Excel 97 SR-2.

Perhaps, there is a misunderstanding: The auditing arrows for references outside the active worksheet are shown differently, i.e. _not_ as blue arrows. Click on the little icon representing a worksheet and then on each of the shown references. This will zap you to the precendent.

If this does not help: Which Excel version and service release do you have ? If unsure: Help|About.

BTW: Did you know you can navigate through the dependents/precendents by clicking on the arrow shaft ? It takes a bit of aim, but it is very handy.

Graded as B, oh well...