How to VBA change cell's value (display text) without changing its formula?

7
votes

I've a problem with this VBA macro.

Sub demoChangeText()
    Application.Range("B2").Value = "Overidden text"
End Sub

My test is here. To run this macro, open it in Excel, press Ctrl+F8 and choose demoChangeText.

As the result, this macro changes the value of cell B2 (the text displayed to us) but clear its formula. I need to change B2's value BUT also need the formula to be remained.

So my question is How to change the display text of cell without changing its formula?

UPDATE

I ask this question because I'm trying to solve this problem

1
vbaexcelformula
Don't think that makes sense. The formula is what defines the display. If you want to change the display, then change the formula. If you change the value to "Overridden Text" you basically change the formula to a trivial one that says "Output this text.." – Jon Egerton
@Davuz ChrisNeilsen answer your question perfectly. I also attached the solution file in his answer below – Nam G VU

1 Answers

12
votes

I'm not sure if this will help, as it is a bit hard to tell what your underlying requirement is, but here goes anyway:

Several things affect the display of a cell:

  1. the entered value, if its a constant
  2. the result of a calculation, if its a formula
  3. the format of the cell
  4. the conditional format(s) of the cell, if any

In the example sheet provided you have a formula of =ROW()&COLUMN() which returns a string result of 22

You can make this display something else by applying a cell format,
eg a format of 0;0;0;Ov\e\r\ri\d\d\e\n t\ext will display any string value as Overridden text

This can by applied with VBA with

Range("B2").NumberFormat = "0;0;0;Ov\e\r\ri\d\d\e\n t\ext\s"

or

Range("B2").NumberFormat = "0;0;0;""Overridden texts"""