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
 

Joe4

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

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Forum statistics

Threads
1,136,290
Messages
5,674,887
Members
419,532
Latest member
longphamtel

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