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 don't think your question is quite clear to us (at least it is not to me, and no one else has responded, so it may be the same for others too).

It might be best to walk us through an actual example with screen images of the "before" and "after" images. You can post screen images using the tool mentioned here: XL2BB - Excel Range to BBCode

Also, please use code tags when posting your VBA code for readability. You can see how to do that here: How to Post Your VBA Code

 

[Wlemi73]

I don't think your question is quite clear to us (at least it is not to me, and no one else has responded, so it may be the same for others too).

It might be best to walk us through an actual example with screen images of the "before" and "after" images. You can post screen images using the tool mentioned here: XL2BB - Excel Range to BBCode

Also, please use code tags when posting your VBA code for readability. You can see how to do that here: How to Post Your VBA Code

Dear Joe4,

Thank you for quick reply.

I don't think your question is quite clear to us (at least it is not to me, and no one else has responded, so it may be the same for others too).

It might be best to walk us through an actual example with screen images of the "before" and "after" images. You can post screen images using the tool mentioned here: XL2BB - Excel Range to BBCode

Also, please use code tags when posting your VBA code for readability. You can see how to do that here: How to Post Your VBA Code

Dear Joe4,

Thank you for quick reply.

Of course. Now I have used code tags. I thik so that now shoud be OK.

I will try explein what I mean. I hope that will be clear for you.

So. In cells from A1 to A20 is formula which compute something like below. The below code which is atatahced in code tag hides these formulas and works very well.
Next step. I would like to change manually for example cell A10 but does not work because this code does not allow to do this.
I would like to change below code in order to change manually cell value.
I tried with Application.EnableEvents =True or False but without success.

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

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

 

[Joe4]

So, I see that if you try to manually change any cell in the range A1:A20, it turns all the formulas in that range to hard-coded values.
Are you saying that you DON'T want that to happen?

What exactly is the purpose of the code you have?
It is written rather strangely for Worksheet_SelectionChange code, so I wonder if there may be a better way to write it so that it does want you want while not interfering with the other tasks you are trying to do.

So can you explain why the need for this code in the first place? What is it supposed to be doing?

 

[Wlemi73]

So, I see that if you try to manually change any cell in the range A1:A20, it turns all the formulas in that range to hard-coded values.
Are you saying that you DON'T want that to happen?

What exactly is the purpose of the code you have?
It is written rather strangely for Worksheet_SelectionChange code, so I wonder if there may be a better way to write it so that it does want you want while not interfering with the other tasks you are trying to do.

So can you explain why the need for this code in the first place? What is it supposed to be doing?

Just I want to hide the formula in order to end user not see this one. The values in cells are a proposal calculated by formula. End user in these cells insert own proposal.

 

[Joe4]

Just I want to hide the formula in order to end user not see this one.

Doing that is not a problem.
The issue is that we will need to modify your current VBA code. But if we aren't sure why it is there or what it is supposed to be doing, we may end up altering it in a way that can cause you other problems.

So we need to understand why it is there and what it is supposed to be doing, so we can alter it accordingly.

 

[Joe4]

Or to put it another way, what issues would it cause to remove that VBA code completely?

 

[Wlemi73]

Or to put it another way, what issues would it cause to remove that VBA code completely?

None issue. Any proposal?

 

[Joe4]

None issue. Any proposal?

If that truly is the case, then just remove the VBA code entirely, and you should be all set.

 

[Wlemi73]

If that truly is the case, then just remove the VBA code entirely, and you should be all set.

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.