For auditing purposes i want to be able to view all the formulas in a
selected range. I can use the Tools/Options menu where I do a View Formulas
but this is used for the entire sheet and I'm only interested in a selected
range.

I have also reformatted the range by code using: cell.value = "'" &
cell.formula which works fine, but I cannot seem to revert the process.

Does anybody have an idea to how I can display formulas in a selected range
in a nice way?

Thank you
The Doctor

Hi Dr.Schwartz

You could assign the following macro to a toolbar button.
The button would toggle the display of formulas for the selection.

You should be aware that displaying formulas means that dependent formulas
may report errors or incorrect results. Re-toggling the display rectifies
this. For this reason, the macro intentionally includes an annoyingly
intrusive messagebox.

As written, formula cells in the selection are additionally highlighted by
applying a background color to the cell. When toggling the display to normal
view, the color is removed.from resurrected formula cells. If this will
interfere with an existing background color arrangement, you can comment
(or remove) the indicated macro clause.

'--------------------8-----------------------
Sub ToggleSelectionFormulas()
Dim rng As Range, rng1 As Range
Dim cell As Range
Dim blFormulasToDisplay As Boolean
Dim res As Long
Dim msg As String

msg = "Formlas outside the selection" _
& vbNewLine _
& "may report incorrect results" _
& vbNewLine & "or show errors until you" _
& vbNewLine _
& "re-toggle the formla display!!" _
& vbNewLine & vbNewLine _
& "Despite this, do you wish to contine?"

If Application.CountA(Selection) = 0 Then Exit Sub

Set rng = Selection

On Error Resume Next
Set rng1 = Intersect(rng, _
rng.SpecialCells(xlCellTypeFormulas))
blFormulasToDisplay = Not rng1 Is Nothing
On Error GoTo 0

If blFormulasToDisplay Then
res = MsgBox(prompt:=msg, _
Buttons:=vbYesNo, _
Title:="WARNING")
If Not res = vbYes Then
MsgBox "Formula display aborted!"
Exit Sub
End If
For Each cell In rng1.Cells
With cell
.Formula = "'" & .Formula
With .Interior '<<=== Optionally exclude
.ColorIndex = 36 '<<=== Optionally exclude
End With '<<=== Optionally exclude
End With
Next
Else
For Each cell In Intersect(rng, _
rng.SpecialCells(xlCellTypeConstants, 2))
With cell
If .PrefixCharacter = "'" Then
If Left(.Value, 1) = "=" Then
.Value = .Value
.Interior.Pattern = xlNone
End If
End If
End With
Next

rng.Replace What:="=", _
Replacement:="=", _
LookAt:=xlPart
End If

End Sub
'---------------------8----------------------<<

---
Regards,
Norman



"Dr.Schwartz" wrote in message
...
For auditing purposes i want to be able to view all the formulas in a
selected range. I can use the Tools/Options menu where I do a View
Formulas
but this is used for the entire sheet and I'm only interested in a
selected
range.

I have also reformatted the range by code using: cell.value = "'" &
cell.formula which works fine, but I cannot seem to revert the process.

Does anybody have an idea to how I can display formulas in a selected
range
in a nice way?

Thank you
The Doctor

Thank you Mr. Jones, just what I was looking for!

"Norman Jones" wrote:

Hi Dr.Schwartz

You could assign the following macro to a toolbar button.
The button would toggle the display of formulas for the selection.

You should be aware that displaying formulas means that dependent formulas
may report errors or incorrect results. Re-toggling the display rectifies
this. For this reason, the macro intentionally includes an annoyingly
intrusive messagebox.

As written, formula cells in the selection are additionally highlighted by
applying a background color to the cell. When toggling the display to normal
view, the color is removed.from resurrected formula cells. If this will
interfere with an existing background color arrangement, you can comment
(or remove) the indicated macro clause.

'--------------------8-----------------------
Sub ToggleSelectionFormulas()
Dim rng As Range, rng1 As Range
Dim cell As Range
Dim blFormulasToDisplay As Boolean
Dim res As Long
Dim msg As String

msg = "Formlas outside the selection" _
& vbNewLine _
& "may report incorrect results" _
& vbNewLine & "or show errors until you" _
& vbNewLine _
& "re-toggle the formla display!!" _
& vbNewLine & vbNewLine _
& "Despite this, do you wish to contine?"

If Application.CountA(Selection) = 0 Then Exit Sub

Set rng = Selection

On Error Resume Next
Set rng1 = Intersect(rng, _
rng.SpecialCells(xlCellTypeFormulas))
blFormulasToDisplay = Not rng1 Is Nothing
On Error GoTo 0

If blFormulasToDisplay Then
res = MsgBox(prompt:=msg, _
Buttons:=vbYesNo, _
Title:="WARNING")
If Not res = vbYes Then
MsgBox "Formula display aborted!"
Exit Sub
End If
For Each cell In rng1.Cells
With cell
.Formula = "'" & .Formula
With .Interior '<<=== Optionally exclude
.ColorIndex = 36 '<<=== Optionally exclude
End With '<<=== Optionally exclude
End With
Next
Else
For Each cell In Intersect(rng, _
rng.SpecialCells(xlCellTypeConstants, 2))
With cell
If .PrefixCharacter = "'" Then
If Left(.Value, 1) = "=" Then
.Value = .Value
.Interior.Pattern = xlNone
End If
End If
End With
Next

rng.Replace What:="=", _
Replacement:="=", _
LookAt:=xlPart
End If

End Sub
'---------------------8----------------------<<

---
Regards,
Norman



"Dr.Schwartz" wrote in message
...
For auditing purposes i want to be able to view all the formulas in a
selected range. I can use the Tools/Options menu where I do a View
Formulas
but this is used for the entire sheet and I'm only interested in a
selected
range.

I have also reformatted the range by code using: cell.value = "'" &
cell.formula which works fine, but I cannot seem to revert the process.

Does anybody have an idea to how I can display formulas in a selected
range
in a nice way?

Thank you
The Doctor