Formula Calculation as Text ( no formula )

[Guest]

Hi,
Can anyone help me with this problem? i have a cell that has a formula. once
the formula has calculated, i want to press a button and "flatten" the
formula.
Example:
A5= Client1
B5= My Project Name
C5= £2500

formula = A5&" "&B5&" "&C5
( Client1 My Project Name £2500 )
want i want is once button ( macro is pressed, when i select the cell, it
shows the finished calculation. ( removing the formula but leaving
calculation showing )

Can this be acheived?

Regards,
Nigel

 

[Don Guillett]

You could use this by right click sheet tab>view code>insert this>SAVE. Now,
when you type in the formula it will automatically be changed to the value.
But it will do with all unless you restrict the cells.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target.Formula = Target.Value
End Sub

 

[Guest]

If you only have a couple of cells you want to do this to,
record a macro
select the cell of interest, copy it and paste special values on the same cell
stop the macro.
in a cell close to the cell of interest draw a box or circle. Right click on
the highlighted shape and assign the macro you just generated.

If you copy the equation back into the cell now you can push the button and
have what you asked for.

This needs to be done for each of the cells of interest.
If you have more cells than you want to do individually, a more formal macro
would need to be written.