hiding formulas

[ehsas69]

I need help for the following two question

1)how i can hide the formula available in the worksheet from users.

2) hoq i can protect the the formula from changing.

 

[Von Pookie]

The answer to both is protecting the sheet.

Select the cells you do *not* want the users to be able to change. Go to Format\Cells\Protection tab and check the box next to "Locked" so that it is selected.

For the cells with formulas, select both "Locked" and "Hidden."

Then protect the sheet.

The users will be unable to change the cells you locked, and those cells that are locked with formulas will not have the formula visible in the formula bar if they are selected.

 

[ehsas69]

Thanks it works.

How I can prevent the formula's from changing when the worksheet is unprotected.

 

[Von Pookie]

When it's unprotected? You can't, not really.

Though I suppose you could *technically* do it if you used a macro to check for changes to the sheet, detect if the changed cell had a formula in it, display a message telling the user they can't change it, then return the formula to the cell...

Which really may be more trouble than it's worth; depending on what you're doing.

 

[ehsas69]

Could I have this macro please.

Thanks

 

[Von Pookie]

Right...

This seems to be working for me, but I doubt I've worked all the kinks out. There are 2 events used for this--the first one will capture the value of the cell when it is selected, the other will return the captured value (that is, the formula) to the cell if it is changed:

Dim ChkFormula As Boolean, PrevFormula As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'if sheet is not protected
If ActiveSheet.ProtectionMode = False Then
    
    'check if selected cell contains formula
    ChkFormula = Target.HasFormula
    
    'if selected cell contains formula, save formula to variable
    If ChkFormula = True Then PrevFormula = Target.Formula
End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

'if sheet is not protected
If ActiveSheet.ProtectionMode = False Then
    
    'if changed cell contains formula
    If ChkFormula = True Then
        Application.EnableEvents = False
        
        'display message box
        MsgBox "This cell contains a formula. " _
        & vbCrLf & "Changing this cell is not allowed.", _
        vbExclamation, "Formula Found"
        
        'replace formula in changed cell
        Target.Formula = PrevFormula
        
        Application.EnableEvents = True
    End If
End If

End Sub

 

[ehsas69]

Thanks a lot it worked