Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
If changed array formula reduce ""\""\""\ - signs to #Missing, will it make ... | Excel Programming |