Programmatically select other sheet precedents or dependents in Excel -

in excel ctrl+[ or ] directly switch sheet show precedents or dependents in sheet.

i want programmatically, because want precedents (or dependents) of selection of cells.

range.dependents , range.precedents have other issues, solution there not solve extra-sheet issue.

mark did job, macro altogether did not go 'dents in same sheet , failed, when there 'dents multiple sheets, since selection cannot created multiple sheet cells.

i needed functionality replace "ctrl + [" , "ctrl + ]" quick shortcut functionality jumping precedents , dependents. unfortunately, these shortcuts unusable on international keyboard, these square brackets buried under altgr (right alt) combination , excel not allow either ctrl+altgr+8 , ctrl+altgr+8 give same result , there no way remap default shortcuts.

so improved code of mark fix these issues , removed pop-up message code, since should know myself if cannot select 'dents, want function work smoothly without me having click ok time. function jumps sheet, linked first in formula.

i hope useful others well.

the thing still bothers me while application.screenupdating = false avoids jumping around sheet , workbook, arrows still keep blinking. way avoid this?

option explicit  private sub getoffsheetdents(byval doprecedents boolean) 'main function, calling separate function find links cells 1 of input cells. works finding precedents whole selection (group of cells) 'doprecedents true, if searching precedents , false, if looking dependents dim inputcell range dim results range dim r range dim sheet worksheet  application.screenupdating = false  each inputcell in application.intersect(activesheet.usedrange, selection) 'cycle go on selected cells. if 1 cell selected, happens once.     set r = onecelldependents(inputcell, doprecedents)     ' r resulting cells each iteration of input cell function.     if not r nothing      'if there precedents/dependents         if sheet nothing  'if first time.             set sheet = r.worksheet             include results, r         elseif not sheet r.worksheet 'if new precedent/dependent on worksheet, don't add selection (gets lost)         else             include results, r         end if     end if next application.screenupdating = true  if results nothing     beep else     results.worksheet.activate     results.select end if end sub  sub getoffsheetdependents() 'function defines, if looking dependents (false) or precedents (true) getoffsheetdents false  end sub  sub getoffsheetprecedents() 'function defines, if looking dependents (false) or precedents (true) getoffsheetdents true  end sub  private function include(byref tounion range, byval value range) range if tounion nothing     set tounion = value     elseif value.worksheet tounion.worksheet 'if new precedent/dependent on same worksheet, add selection             'if new precedent/dependent on worksheet, don't add selection (gets lost)         set tounion = application.union(tounion, value) end if set include = tounion end function  private function onecelldependents(byval inrange range, optional doprecedents boolean) range 'function finds dependents 1 of selected cells. happens once, if 1 cell selected. dim inaddress string, returnselection range dim long, pcount long, qcount long application.screenupdating = false if inrange.cells.count <> 1 error.raise 13 'seems check, 1 cell handled, not seem necessary step.  'remember selection set returnselection = selection ' keep initial selection getoffsheetdents function. inaddress = fulladdress(inrange) ' takes address of starting cell analyzed. pcount = 1  inrange   'all functions apply initial cell.     .showprecedents     .showdependents     .navigatearrow doprecedents, 1 ' go first precedent (if first argument true)/dependent. why required?     until fulladdress(activecell) = inaddress         .navigatearrow doprecedents, pcount 'go first precedent, second etc.         if activesheet.name <> returnselection.parent.name ' checks, if precedent not on same sheet                              qcount = qcount + 1   'qcount follows external references, if arrow external reference arrow.                 .navigatearrow doprecedents, pcount, qcount 'go first exteranl precedent, second etc.                 include onecelldependents, selection                 on error resume next                 .navigatearrow doprecedents, pcount, qcount + 1 'could remove step , check error before include?                 if err.number <> 0 exit                 on error goto 0  ' not sure if used, since if there error, exit in previous step.             loop             on error goto 0 'not sure, if necessary, since asked in loop.         else  ' if precedent on same sheet.             include onecelldependents, selection         end if         pcount = pcount + 1         .navigatearrow doprecedents, pcount     loop     .parent.cleararrows end  'return selection returnselection     .parent.activate     .select end  end function  private function fulladdress(inrange range) string 'function takes full address sheet name  inrange     fulladdress = .parent.name & "!" & .address end end function