hide and lock cells with formula using vba

hi,

anybody knows how to hide formulas and lock that cell with formula using a macro? is this possible to do for the entire worksheets of the workbook?

thanks,

stoey

A possible way without looping the cells

Option Explicit

Sub LockFormulae()
    Dim rngHasFormula As Range
    Dim ws As Worksheet
    
    For Each ws In ActiveWorkbook.Worksheets
        ws.Unprotect
        Set rngHasFormula = ws.UsedRange.SpecialCells(xlCellTypeFormulas)
        With rngHasFormula
            .Locked = True
            .FormulaHidden = True
        End With
        ws.Protect
    Next
End Sub

The code should be in a standard module not the workbook module.

1/. In Excel press Alt+f11 this will open the VBa editor.

2/. Select the "Insert" tab > Module

3/. Paste all of the given code in the resultant pane/window.

4/. Save the file and close the VBa editor

5/. In Excel (2007) Developer > Macros.... select the one you need then Press "Run"

You can put this into any module (sheet, workbook or macro module)
Remove 'Option Explicit'

Sub snb()
 on error resume next
 
 For Each sh In sheets
   with sh.cells.SpecialCells(xlCellTypeFormulas)
     if err.number=0 then 
       sh.unprotect
       .Locked = True
       .FormulaHidden = True
     end if
   End With
   err.clear
 Next
End Sub

Do you have any cells with formulas in them? If not then you are going to get an error message. You can get around this by adding:

On Error Resume Next

right after the declarations, but you should be sure that this is your only problem before supressing all error messages.

Hope this helps.

abousetta

I have to learn to type faster . My post is just a redundancy of what Marcol already said.

abousetta

Okay that means there are no formulae in that particular worksheet.

Try this code

Option Explicit

Sub LockFormulae()
    Dim rngHasFormula As Range
    Dim ws As Worksheet

    On Error Resume Next
    For Each ws In ActiveWorkbook.Worksheets
        ws.Unprotect
        Set rngHasFormula = ws.UsedRange.SpecialCells(xlCellTypeFormulas)
        If Err.Number <> 0 Then
            Err.Clear
        Else
            With rngHasFormula
                .Locked = True
                .FormulaHidden = True
            End With
        End If
        ws.Protect
    Next
    On Error GoTo 0
End Sub

I consider it to be a superstition.
Arguments can convince me, authority can't.

Originally Posted by snb

I consider it [Option Explicit] to be a superstition.
Arguments can convince me, authority can't.

The number of misspellings that Option Explicit has caught for me have convinced me to use it.

I could see someone who didn't feel they needed it, but I can't think of any argument why its presence is a bad idea.

Its not a lack of hope that misspells J, its an excess of hop.

Beer? ... yum yum! ... burp ... ...