Temporarily change an Excel's cell displaying value while updating values-CodePudding

I want to design a form to proportionally subtract a number (which already entered in an Userform TextBox) from two specific cells and temporarily see the outcome without actually/permanently changing those cell values. (For example subtracting number 54 proportionally from two cells which currently have 353 and 158 as their values)

CodePudding user response：

This is not exactly "elegant", but I suppose you could use a custom number format to have the cell show a different value than whatever it actually contains.

Sub run_demo() 'temporarily formats A1 to show custom message
  showValue ActiveSheet.Range("A1"), "Please wait!"
End Sub

Sub showValue(myCell As Range, tmpValue As String)
  Dim origFormat As String

  origFormat = myCell.NumberFormat 'store original cell format
  myCell.NumberFormat = """" & tmpValue & """"  'display custom message

  '...now do whatever u gotta do:
  Application.CalculateFull 'for example you could force recalculation
  pause 3  '(pausing 3 seconds, just for demo)
  
  myCell.NumberFormat = origFormat 'replace original cell format
End Sub

Sub pause(sec As Single)
  Dim t As Single: t = Timer 'get current time
  Do: DoEvents: Loop While Timer < t   3 'wait til [sec] secs elapsed
End Sub

...but I guess if you're going to bother with that, you could also just store the actual cell value (unless it is relevant to sheet recalculation, in which case this method would work since the cell's formatting won't affect the calculation -- in theory, anyway.)

^{I'm not clear on your goal (your question is a little vague and lacking in any examples of what you've tried) but regardless, I can't help but feel that there's probably a better way to do it. (I sometimes find it helpful to "take a step back", re-analyze your goal, and tackle it from a different angle.)}