turn formula to value

[lior03]

hello
i want to prevent a user from seeing my formula .
i want him to see values.
i came up with this:

selection.Locked = True
selection.FormulaHidden = True
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True


the problem is - this macro force me to protect the sheet.second
i dot see a thing in the formual bar.
any suggestions?
thanks

[Bob Phillips]

So you are saying that you don't want the formula to be seen, and then are complaining that you can't see the formula?

As they say, you can't have your cake and eat it.

[lior03]

dear xld
i have a formula in cell which i do not want a user to see.i want him to see a value.
if a cell has a sum formula in it which result in 5 i want the user to see 5 and no formula.
thanks

[lior03]

dear xld
i have a formula in cell which i do not want a user to see.i want him to see a value.
if a cell has a sum formula in it which result in 5 i want the user to see 5 and no formula.
thanks

[rory]

The point is, if you hide the formula, you won't see anything in the formula bar. (you can't have a formula but see the result of the formula in the formula bar)
You can either protect the sheet and get the behaviour you have, or you can copy the formulas and paste them as values.
Regards,
Rory

[lior03]

so how do i prevent a user see my formula but keep the value in the cell?

[Bob Phillips]

The value IS IN THE CELL. You won't see that value in the formula bar, becuase the formula bar shows the formula, and you have hidden that.

[lior03]

could tou at least show how to add a password to a protected activesheet so a user can not change it?
thanks

[rory]

You can use:
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="password"

Regards,
Rory

[Bob Phillips]

There is a tool called the macro recorder. It will show you basic things like this.

[lior03]

If IsEmpty(selection) Then Exit Sub
selection.Copy
selection.PasteSpecial paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveSheet.paste
Application.CutCopyMode = False