Hidden formula/possibility of change of cell

Wlemi73

New Member
Joined
Oct 8, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,249
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 8, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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.


1603195984353.png


VBA Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,249
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 8, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,249
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,249
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,249
Office Version
  1. 365
Platform
  1. Windows
None issue. Any proposal?
If that truly is the case, then just remove the VBA code entirely, and you should be all set.
 

Wlemi73

New Member
Joined
Oct 8, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,136,201
Messages
5,674,384
Members
419,504
Latest member
tismail

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top