Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
I want to protect a cell but allow formula values only
Hello, i have a worksheet with some formulas in it. I
have the sheet protected, but there are some cells i cannot protect because if i do, values calculated by formulas will not populate them. When i leave them unprotected, there is a risk of people typing their own value in and erasing the formula. How can i truly protect my sheet? I want to alllow data to be populated in certain cells, but only by formulas, not by a user typing in whatever they want. Your help greatly appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
I want to protect a cell but allow formula values only
Values calculated by formulas will continue to be updated despite sheet
protection. -- Vasant "Fredy A." wrote in message ... Hello, i have a worksheet with some formulas in it. I have the sheet protected, but there are some cells i cannot protect because if i do, values calculated by formulas will not populate them. When i leave them unprotected, there is a risk of people typing their own value in and erasing the formula. How can i truly protect my sheet? I want to alllow data to be populated in certain cells, but only by formulas, not by a user typing in whatever they want. Your help greatly appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
I want to protect a cell but allow formula values only
Hi
you can simply protect the cells with formulas. they will get calculated if the cells referenced by this formula are changed. So no problem protecting your formula cell. -----Original Message----- Hello, i have a worksheet with some formulas in it. I have the sheet protected, but there are some cells i cannot protect because if i do, values calculated by formulas will not populate them. When i leave them unprotected, there is a risk of people typing their own value in and erasing the formula. How can i truly protect my sheet? I want to alllow data to be populated in certain cells, but only by formulas, not by a user typing in whatever they want. Your help greatly appreciated. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
I want to protect a cell but allow formula values only
Put this in your worksheets SelectionChange event. this will automatically force the selection down by one row anytime it encounters a cell with a formula. You can change it to 'Target.Offset(0, 1).Select' to force a column change depending on how your woksheet flow is directed. also you may have save and close the workbook the first time to activte i
Private Sub Worksheet_SelectionChange(ByVal Target As Range If Target.Cells.Count = 1 The If Target.HasFormula Then Target.Offset(1, 0).Selec End I End Su ----- Fredy A. wrote: ---- Hello, i have a worksheet with some formulas in it. I have the sheet protected, but there are some cells i cannot protect because if i do, values calculated by formulas will not populate them. When i leave them unprotected, there is a risk of people typing their own value in and erasing the formula. How can i truly protect my sheet? I want to alllow data to be populated in certain cells, but only by formulas, not by a user typing in whatever they want. Your help greatly appreciated. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
I want to protect a cell but allow formula values only
In the sheet's change event undo any non-formula entry.
Might be difficult to implement in the real world for a variety of reasons but here's a start. Private Sub Worksheet_Change(ByVal Target As Range) If Not Target(1, 1).HasFormula Then Application.Undo End If End Sub "Fredy A." wrote in message ... Hello, i have a worksheet with some formulas in it. I have the sheet protected, but there are some cells i cannot protect because if i do, values calculated by formulas will not populate them. When i leave them unprotected, there is a risk of people typing their own value in and erasing the formula. How can i truly protect my sheet? I want to alllow data to be populated in certain cells, but only by formulas, not by a user typing in whatever they want. Your help greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protect cell but allow formula to work | Excel Discussion (Misc queries) | |||
Protect individual cell and hide formula in formula bar | Excel Worksheet Functions | |||
Locking cell to protect the formula | Excel Discussion (Misc queries) | |||
How do I protect a cell formula from being overwritten | Excel Discussion (Misc queries) | |||
How do I protect the cell from users entering values in Excel? | Excel Worksheet Functions |