Hidden formula/possibility of change of cell

[Wlemi73]

Dears,

I would like to change below code in order to change manually cell value.
For instance in range A1:A20 I have hide formula which display result (value) 1,2,3,...,20 and I want change manually 20,20,20,...,20 but does not work.
This code deos not allow.
I tried with Application.EnableEvents =True or False but without success.
Could you please let me any clue?

Thank you in advance for your quick reply and your help and time
Yours sincerely


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Rng As Range
Static Started As Boolean
Static Cell As Range
Static TheFormula As String
Set Rng = Range("A1:A20")
If Not Application.Intersect(ActiveCell, Rng) Is Nothing Then
If Not Cell Is Nothing Then
Cell.Formula = TheFormula
End If
Set Cell = ActiveCell
With Cell
TheFormula = .Formula
.Value = .Value
End With
Started = True
Else
If Started Then
With Cell
.Formula = TheFormula
End With
End If
End If
End Sub

 

[Joe4]

I did not expect such the solution Maybe otherwise. As I mentioned before I need to hide formula in excel cell from end user but I also want in order to end user can change this cell.

I am not sure what you are saying. Are you saying that it works for you, or is there some other issue?

What you had before did not hide any formula from the user. It just converted the formulas to hard-coded values. Once you do that, the formulas cease to exist.
A cell in Excel can either have a formula, or a hard-coded value, but never both at the same time.