VBA to "Label" and "Unlabel" formula
 

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

The purpose of this is to see the effect on a sum, or to temporaril
"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 ge
it to work.

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

--
brandtboo
-----------------------------------------------------------------------
brandtboo0's Profile: http://www.excelforum.com/member.php...fo&userid=3457
View this thread: http://www.excelforum.com/showthread.php?threadid=54344

VBA to "Label" and "Unlabel" formula
 

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

VBA to "Label" and "Unlabel" formula
 

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


--
brandtboo0
------------------------------------------------------------------------
brandtboo0's Profile: http://www.excelforum.com/member.php...o&userid=34574
View this thread: http://www.excelforum.com/showthread...hreadid=543448

VBA to "Label" and "Unlabel" formula
 

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.

VBA to "Label" and "Unlabel" formula
 

Thanks for the help, Executor.


--
brandtboo0
------------------------------------------------------------------------
brandtboo0's Profile: http://www.excelforum.com/member.php...o&userid=34574
View this thread: http://www.excelforum.com/showthread...hreadid=543448