VBA to "Label" and "Unlabel" formula

[brandtboo0]

I am looking for code to turn a formula into a label, then move down one cell. Also code to undo (unlabel) the cell.

The purpose of this is to see the effect on a sum, or to temporarily "turn off" formula cells. For example -

In the column below, where all numbers are formulas -

10 (this cell is the formula =+A1)
20 (this cell is the formula =+A2)
30 (this cell is the formula =+A3)
-----
60
-----

10
20
=+A3 < this is what I want the VBA macro to do
-----
30
-----

I tried "Edit" "Home" " ' " "Enter" interactively, but I couldn't get it to work.

The equivalent in Lotus would be {EDIT}{HOME}'~{D}

[Executor]

Hi Brandtboo0

I have cooked this in Excel 2000:

Public Sub ToggleFunction()
If Not IsEmpty(ActiveCell) Then
If ActiveCell.HasFormula Then
ActiveCell.Value = "'" & ActiveCell.Formula
Else
If Not IsNumeric(ActiveCell) Then
ActiveCell.Formula = ActiveCell.Text
End If
End If
End If
End Sub


HTH,

Executor

[brandtboo0]

Your solution worked perfectly, but I failed to make the distinction between a formula and a regular number.

It worked on " =+a7 " but not on " 55 " or +55+44 "

Sorry I didn't make that clear. Is there a modification that would fix it?

Thanks,
brandtboo0

[Executor]

Hi brandtboo0,

If it is not a problem using a # in front of formules and fugures:

Public Sub ToggleFunction()
Dim strContents As String
If Not IsEmpty(ActiveCell) Then
If ActiveCell.HasFormula Then
ActiveCell.Value = "#" & ActiveCell.Formula
Else
If Not IsNumeric(ActiveCell) Then
strContents = ActiveCell.Text
If Left(strContents, 2) = "#=" Then
ActiveCell.Formula = Mid(strContents, 2)
Else
ActiveCell.Value = Mid(strContents, 2)
End If
Else
ActiveCell.Value = "#" & ActiveCell.Text
End If
End If
End If
End Sub

HTH


Executor.

[brandtboo0]

Thanks for the help, Executor.