Happens only once, if initially only one cell selected.ĭim inAddress As String, returnSelection As Rangeĭim i As Long, pCount As Long, qCount As Long 'Function finds dependents for one of the selected cells. Private Function oneCellDependents(ByVal inRange As Range, Optional doPrecedents As Boolean) As Range Set ToUnion = Application.Union(ToUnion, Value) 'if new precedent/dependent is on another worksheet, don't add to selection (gets lost) Private Function Include(ByRef ToUnion As Range, ByVal Value As Range) As RangeĮlseIf Value.Worksheet Is ToUnion.Worksheet Then 'if new precedent/dependent is on the same worksheet, then add to selection 'Function defines, if we are looking for Dependents (False) or Precedents (True) If sheet Is Nothing Then 'if this is the first time.ĮlseIf Not sheet Is r.Worksheet Then 'if new precedent/dependent is on another worksheet, don't add to selection (gets lost) If Not r Is Nothing Then 'if there were precedents/dependents ' r is resulting cells from each iteration of input cell to the function. Set r = oneCellDependents(InputCell, doPrecedents) If only one cell selected, then happens only once. 'Cycle to go over all initially selected cells. 'doPrecedents is TRUE, if we are searching for precedents and FALSE, if looking for dependentsįor Each InputCell In Application.Intersect(ActiveSheet.UsedRange, Selection) Works for finding precedents for a whole selection (group of cells) 'Main function, calling for separate function to find links to all cells to one of the input cells. Private Sub GetOffSheetDents(ByVal doPrecedents As Boolean) The only thing what still bothers me is that while Application.ScreenUpdating = False Avoids jumping around the sheet and workbook, the arrows still keep blinking. I hope this is useful for others as well. So the function just jumps to the sheet, which is linked first in the formula. So I improved the code of Mark slightly to fix these issues and removed the pop-up message from code, since I should know myself if I cannot select all 'dents, but I want the function to work smoothly without me having to click OK all the time. Unfortunately, these shortcuts are completely unusable on international keyboard, where these square brackets are buried under AltGr (right Alt) combination and Excel does not allow either Ctrl+AltGr+8 and Ctrl+AltGr+8 to give the same result and also there is no way to remap the default shortcuts. I personally needed all this functionality to replace the "Ctrl + " quick shortcut functionality for jumping to precedents and dependents. Mark did some good job, but this macro altogether did not go to 'dents in the same sheet and failed, when there were 'dents from multiple sheets, since the selection cannot be created from multiple sheet cells.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |