VBA macro for dependents

[gaddjun]

Hi

I am trying to make a macro code for navigating dependents with shortcut menu.

For example,
Given that B1=A1 and C1=A1,
if I run the macro on A1, a shortcut menu pops up and I can select B1 or C1 to move(activate) to the cell.

I failed to make the shortcut menu, so would like to have your advice.

Thank you.

———

Sub messageBoxCellDependents()
Dim SelRange As Range
Set SelRange = Selection
Application.Commandbars(“findDepend(SelRange)”).ShowPopup
End Sub

Function fullAddress(inCell As Range) As String
fullAddress = inCell.Address(External:=True)
End Function

Function findDepend(ByVal inRange As Range) As String
Dim sheetIdx As Integer
sheetIdx = Sheets(inRange.Parent.Name).Index

If sheetIdx = Worksheets.Count Then 'vba bug workaround
Sheets(sheetIdx - 1).Activate
Else
Sheets(Worksheets.Count).Activate
End If

Dim inAddress As String, returnSelection As Range
Dim i As Long, pCount As Long, qCount As Long
Set returnSelection = Selection
inAddress = fullAddress(inRange)

Application.ScreenUpdating = False
With inRange
.ShowPrecedents
.ShowDependents
.NavigateArrow False, 1
Do Until fullAddress(ActiveCell) = inAddress
pCount = pCount + 1
.NavigateArrow False, pCount
If ActiveSheet.Name <> returnSelection.Parent.Name Then
Do
qCount = qCount + 1
.NavigateArrow False, pCount, qCount
findDepend = findDepend & fullAddress(Selection) & Chr(13)

On Error Resume Next
.NavigateArrow False, pCount, qCount + 1
Loop Until Err.Number <> 0
.NavigateArrow False, pCount + 1
Else
findDepend = findDepend & fullAddress(Selection) & Chr(13)

.NavigateArrow False, pCount + 1
End If
Loop
.Parent.ClearArrows
End With

With returnSelection
.Parent.Activate
.Select
End With

Sheets(sheetIdx).Activate 'activate original worksheet
End Function

Reference:
https://excelhelphq.com/how-to-find-all-dependent-cells-outside-of-worksheet-and-workbook-in-excel-vba/

[大灰狼1976]

Hi gaddjun!
Not sure, maybe:
change:

Application.Commandbars(“findDepend(SelRange)”).ShowPopup
into:

Application.Commandbars(findDepend(SelRange)).ShowPopup

[gaddjun]

I tried but got error message:

Run-time error 9
Subscript out of range

T_T

[大灰狼1976]

I can't download your attachment because of the network restriction. Can you upload it here?

[gaddjun]

Hi! What you meant “attachment” is the code?

[大灰狼1976]

Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)

[gaddjun]

I am sorry that I cannot attach the file. The file is in my computer for work and that is restricted to upload any file on the internet. Only thing I can share with you is the code in my original question. My apologies.

[大灰狼1976]

"Run-time error 9
Subscript out of range"
This error is not due to the generated menu itself, but to the wrong values.
So I want to take a look at workbook. No file may not find the reason.

[gaddjun]

Thank you very much. Let me upload the file later with my private computer.
Meanwhile, I tried again as your advice (eliminating “” in CommanBars index) and another error message came out::

Run-time error ‘5’
Invalid procedure call or argument

[大灰狼1976]

This is a custom function call error, but also need debugging to find out the reason.

[gaddjun]

Hi, please find the attached file. Sorry for my late response.

[大灰狼1976]

The return value of findDepend is "[VBA_Dependent.xlsm]Sheet2!$B$3" & chr(10) & "[VBA_Dependent.xlsm]Sheet1!$B$3" & chr(10) & "[VBA_Dependent.xlsm]Sheet1!$B$3"
Are you sure that's what you want? Commbars can't recognize this string.

[gaddjun]

Thanks. Yes, the return values I expect are them. If Commbars cannot recognize, is there any solution?

[大灰狼1976]

Note that "[VBA_Dependent.xlsm]Sheet2!$B$3" and others are strings, not variables.
Unless you first add a new object with this name. And special symbols may not be allowed in name.

[大灰狼1976]

A simple example from internet.

Sub createBar()
Set myBar = CommandBars _
.Add(Name:="Custom", Position:=msoBarPopup, Temporary:=False)
With myBar
.Controls.Add Type:=msoControlButton, ID:=3
.Controls.Add Type:=msoControlComboBox
End With
myBar.ShowPopup
End Sub

Sub delBar()
CommandBars("Custom").Delete
End Sub

[Jan Karel Pieterse]

Hmm, there is a lot to think about when tracing dependents and precedents, which I have combined into: https://jkp-ads.com/reftreeanalyser.asp free demo.