Type of Drill-Down View

[Guest]

*** I Have also posted this Q to the General Section***

In Sheet1 - Cell B4 there is =345.54+58.16+100.50-45.78+52.16 'Displayed
as $510.58
In my Sheet31 Cell B6 there is =Sheet1!B4 ' Displayed as $510.58

I need to prove a way for the user to right-click on Cell B6 of Sheet31 and
somehow produce a vertical Listing showing (maybe in a Message box):
345.54
+58.16
+100.50
-45.78
+52.16

Can this be done?
I can do this in another cell by utilizing the following UDF
Function SeeValues(Activecell As Range) As Variant
Application.Volatile True
SeeValues = Activecell.Formula
End Function

Any assistance appreciated.

Jim

 

[Guest]

Instead of on right click I went with double click. If you have your heart
set on right click it is an easy switch but double click seems a little nicer
to me... Right click the sheet tab that you want this code to work in and
select view code. Pate the following and you should be good to go. It lists
all of the precident formulas of the cell that is double clicked.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim strFormula As String
Dim rng As Range

On Error Resume Next
strFormula = Target.Formula
For Each rng In Target.Precedents
strFormula = strFormula & vbCrLf & rng.Formula
Next rng
MsgBox strFormula
End Sub

 

[Guest]

When I double-click on Cell B6 of Sheet31 the message box pops-up
giving me only =Sheet1!B4 << the same as is in my formula bar
Something else is going on - Can you double-check your code
Thank you so much for assisting!!
I added a line Cancel = True << to negate the edit function from turning on
Jim

 

[Guest]

Jim:

It looks like the line:
For Each rng In Target.Precedents << after running the first time shows rng
= nothing

 

[Tom Ogilvy]

the precedents command doesn't work with references on other sheets.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As _
Range, Cancel As Boolean)
Dim strFormula As String
Dim rng As Range, v As Variant
Dim sh As Worksheet, s As String
Dim i As Long, sChr As String

Cancel = True
If Not Target.HasFormula Then Exit Sub
strFormula = Target.Formula
v = Split(Replace(strFormula, "=", ""), "!")
Set sh = Worksheets(v(LBound(v)))
Set rng = sh.Range(v(UBound(v)))
strFormula = rng.Formula
s = ""
For i = 1 To Len(strFormula)
sChr = Mid(strFormula, i, 1)
Select Case sChr
Case "+", "-", "*", "\", "/"
s = s & vbNewLine & sChr
Case "="

Case Else
s = s & sChr
End Select
Next i
MsgBox s
End Sub

 

[Guest]

Awesome Tom;
Much appreciated
Jim May

the precedents command doesn't work with references on other sheets.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As _
Range, Cancel As Boolean)
Dim strFormula As String
Dim rng As Range, v As Variant
Dim sh As Worksheet, s As String
Dim i As Long, sChr As String

Cancel = True
If Not Target.HasFormula Then Exit Sub
strFormula = Target.Formula
v = Split(Replace(strFormula, "=", ""), "!")
Set sh = Worksheets(v(LBound(v)))
Set rng = sh.Range(v(UBound(v)))
strFormula = rng.Formula
s = ""
For i = 1 To Len(strFormula)
sChr = Mid(strFormula, i, 1)
Select Case sChr
Case "+", "-", "*", "\", "/"
s = s & vbNewLine & sChr
Case "="

Case Else
s = s & sChr
End Select
Next i
MsgBox s
End Sub