Excel VBA Tracing Precedence in a protected worksheet

[r.raw75]

In a protected worksheet I have been unable to trace precedent, so I have found some VBA code that achieves this for me. I have put the code in and it does trace precedents on a protected sheet by double clicking on the cell with the link. However the instructions say that there should be a temporary command bar that appears and it does not, can you help? On this temporary command bar should be an icon that can be pressed to trace precedents.

Also I would like to know how I can add this to all my workbooks so that when I send them to my colleagues overseas they can then trace precedents in protected workbooks.

teh code is as following:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
KillBar
End Sub

Private Sub Workbook_Open()
MakeBar
End Sub


**** New Code Module *****

Option Explicit
Public Const TempAuditBar = "Temp Audit Bar"
Public Const MyPassword = "integrity"

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()
ThisWorkbook.ActiveSheet.Unprotect MyPassword
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
ThisWorkbook.ActiveSheet.Protect MyPassword
End Sub

Sub KillBar()
On Error Resume Next
Application.CommandBars(TempAuditBar).De…
On Error Goto 0
End Sub

can somebody please tell me where I may be going wrong? Many thanks

The VBA manual I downloaded from http://www.mousetraining.co.uk/training-manuals/Excel2003VBA.pdf

doesn't seem to provide me the answer!

Cheers

R

 

[RoryA]

Which version of Excel are you using?

 

[r.raw75]

I tried this both in 2003 and 2007!

If you could tell me in both teh versions then it'll be great!

Cheers
R

 

[RoryA]

You can't build a new floating toolbar in 2007 due to the new menu system, which replaces the old commandbars.
However, I don't really see why you can't just use the Formula Auditing toolbar anyway - that's what it's for!

 

[r.raw75]

The main reason I can't do this is bexcause in Excel 2003 when you protect your worksheet the Tracing DEpedents toolbar becomes de activated!

Therefore I was wondering if there are any way of protecting the worksheet but still be able to allow people to trace dependents and precedents!!

 

[RoryA]

I've just tested your code in 2003 (after correcting typos) and the toolbar does appear. I presume you have macros enabled? (I also presume you know that worksheet protection is almost worthless)

 

[r.raw75]

Dear Royra

Thanks for your repsonse

I would highly appreciate it if you could copy and paste the code that you tested the macro with. I am not sure which typos you are mentioning! I have a very limited understanding of Macros so I just copied and pasted tehe macro hoping that it will work!

Do you also know how this can be on a workbook so that if I have to send teh workbok via e-mail to my colleagues they will be able to see teh toolbar as well! The worksheet still needs to be protected!

 

[RoryA]

The only things I changed were where you for some reason had ... appearing instead of something else. Specifically, I changed this in MakeBar:

Set NewMenu = Application.CommandBars.Add(TempAuditBar… msoBarFloating,  False, True)

to this:

Set NewMenu = Application.CommandBars.Add(TempAuditBar, msoBarFloating,  False, True)

and this in KillBar:

Application.CommandBars(TempAuditBar).De…

to this:

Application.CommandBars(TempAuditBar).Delete

If your code is in the workbook, then it will be available to your colleagues after you email the file to them.

 

[r.raw75]

Many thanks

I appreciate it!

 

[brettdj]

That code looks familiar

http://www.vbaexpress.com/kb/getarticle.php?kb_id=255

stumbled on this thread after an "audit" keyword search

Cheers

Dave