VBA for Dependents shortcut menu


Joined
May 22, 2019
Messages
1
Reaction score
0
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/
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top