none
Unable to open trace dependent dashed arrow on protected sheet RRS feed

  • Question

  • I have worked out some VBA code that allows me to show the dependent/precedent arrows from the selected cell on my protected worksheet.

    However, whenever there is a dashed black line dependent/precedent arrow signifying dependents/precedents from another sheet, I am unable to double click on it to open the reference box that would show the dependent/precedent cell references if the sheet was not protected. I have found that if I set the DrawingObjects to False when protecting the sheet that it will allow me to double click and open the reference box.

    However, I have several form control objects on the sheet that I do not want to be edited or moved by anyone which makes setting DrawingObjects to False a non-option. I need to have the trace dependents/precedents functionality but still keep my form controls protected.

    Is there a way for me to be able to double click on that dashed line after I show the dependents/precedents on a protected sheet?

    I did some research and below is the code I entered into a module to create a Command Bar with options to trace dependents or precedents. When you open the file a new tab labeled "ADD-INS" appears and the command bar is found there. The trace dependent and trace precedent options on that tab work so far as showing the dependent/precedent arrows, however, the dashed arrow for dependents/precedents on other sheets does not allow me to double click on it to bring up the reference box that normaly allows you to select and go to dependent/precedent references:

    Option Explicit
    Public Const TempAuditBar = "Temp Audit Bar"
    
    Sub MakeBar()
        Dim NewMenu As CommandBar
        Dim Ctrl As CommandBarControl
        Dim i As Integer
    
        KillBar
    
        Set NewMenu = Application.CommandBars.Add(TempAuditBar, msoBarFloating, False, True)
    
        With NewMenu
            .Controls.Add Type:=msoControlButton, ID:=486
            .Controls.Add Type:=msoControlButton, ID:=452
            .Controls.Add Type:=msoControlButton, ID:=451
            .Controls.Add Type:=msoControlButton, ID:=450
            .Controls.Add Type:=msoControlButton, ID:=453
        End With
    
        For Each Ctrl In NewMenu.Controls
            Ctrl.OnAction = ThisWorkbook.Name & "!TP"
        Next
    
        With NewMenu
            .Visible = True
            .Protection = msoBarNoChangeVisible
        End With
    End Sub
    
    Sub TP()
        Select Case Application.CommandBars.ActionControl.ID
        Case 486
            Selection.ShowPrecedents
        Case 452
            Selection.ShowPrecedents Remove:=True
        Case 451
            Selection.ShowDependents
        Case 450
            Selection.ShowDependents Remove:=True
        Case 453
            ActiveSheet.ClearArrows
        End Select
    End Sub
    
    Sub KillBar()
        On Error Resume Next
        Application.CommandBars(TempAuditBar).Delete
        On Error GoTo 0
    End Sub

    The subs in this module are called in the following code i placed in the ThisWorkbook Object:

    Private Sub Workbook_Open()
        MakeBar
    End Sub
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        KillBar
    End Sub





    • Edited by semin0le Wednesday, June 24, 2015 9:34 PM
    Wednesday, June 24, 2015 5:55 PM

All replies

  • Hi semin0le,

    >> I am unable to double click on it to open the reference box that would show the dependent/precedent cell references if the sheet was not protected

    What do you mean by "cell references"? Is it the formula in the cell? Did you mean you are unable to open the reference under the protected sheet or unprotected sheet?

    >> I have found that if I set the DrawingObjects to False when protecting the sheet that it will allow me to double click and open the reference box.

    What do you mean by "set the DrawingObjects to False"? Could you share us how you set it?

    >> Is there a way for me to be able to double click on that dashed line after I show the dependents/precedents on a protected sheet?

    Do you mean that when you double click on the dependent/precedent cell in the unprotected sheet, it will show the formula of the cell, when you double click on the dependent/precedent cell in the protected sheet, it will not, and you want to show the formula of the cell in the projected sheet? If so, I am afraid there is no way to achieve your requirement. In a protected worksheet, you could only view the worksheet, and you could not interop with the worksheet like double clicking the cell.

    If I misunderstood you, please feel free to let me know.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, June 26, 2015 8:40 AM