Hide formulas using VBA

[Tom Carlson]

I have a worksheet that has formulas that I don't want the users to see or
change. I can use the Protect feature in the Tools menu and that works fine
until I want to copy the worksheet. What I want to do is hide/protect the
formulas using VBA, unprotect them so I can copy the worksheet, then
hide/protect them in the new, copied worksheet and then re-protect them in
the original worksheet. I've tried various things but nothing seems to
work. Right now I have this code to protect the range of cells that contain
the formulas:

Sub protect()
Range( "A3:A12,D3:E12,J1:R13,S3:S11,W18").Select
Range("W18").Activate
Selection.Locked = True
ActiveSheet.protect Contents:=True
End Sub

Then I use this code to add a new worksheet:

Sub AddNewSheet()
ActiveSheet.Unprotect
Cells.Select
Selection.Copy
Sheets.Add
ActiveSheet.Paste
Application.CutCopyMode = False

Call protect
End Sub

When the protect routine runs the very first time, it works fine. But when
I unprotect the cells in the AddNewSheet routine, they never get
re-protected nor do the cells in the copied sheet get protected. I'd
appreciate any help. Thanks very much.

Tom

[William]

Tom

Sub Macro1()
Application.ScreenUpdating = False
Dim ws As Worksheet
With Sheets("Sheet1")
.Unprotect
.Cells.Locked = False
.Cells.SpecialCells(xlCellTypeFormulas, 23).Locked = True
.Cells.SpecialCells(xlCellTypeFormulas, 23).FormulaHidden = True
Set ws = Sheets.Add
.Cells.Copy ws.Range("A1")
End With
With ws
.Unprotect
.Cells.Locked = False
.Cells.SpecialCells(xlCellTypeFormulas, 23).Locked = True
.Cells.SpecialCells(xlCellTypeFormulas, 23).FormulaHidden = True
.Protect
End With
Sheets("Sheet1").Protect
Application.ScreenUpdating = True
end sub

Regards

William
willw...@yahoo.com

"Tom Carlson" <tcarl...@earthlink.net> wrote in message
news:uHB9fZS0AHA.2324@tkmsftngp02...

[William]

Tom

The code can be simplified to this

Sub Macro1()
Application.ScreenUpdating = False
Dim ws As Worksheet
With Sheets("Sheet1")
.Unprotect
.Cells.Locked = False
.Cells.SpecialCells(xlCellTypeFormulas, 23).Locked = True
.Cells.SpecialCells(xlCellTypeFormulas, 23).FormulaHidden = True
Set ws = Sheets.Add
.Cells.Copy ws.Range("A1")

ws.Protect
.Protect
End With
Application.ScreenUpdating = True
End Sub

"William" <willw...@yahoo.com> wrote in message
news:u9TBXpS0AHA.1364@tkmsftngp05...

[Orlando Magalhães Filho]

Hi Tom,

See these changes on your Sub:

Sub AddNewSheet()
Dim FirstSh As Worksheet
Set FirstSh = ActiveSheet

ActiveSheet.Unprotect
Cells.Select
Selection.Copy
Sheets.Add
ActiveSheet.Paste
Application.CutCopyMode = False

Call protect

FirstSh.Select

ActiveSheet.protect Contents:=True
End Sub

HTH

--
Orlando Magalhães Filho
Santa Quitéria-Ce, Brazil
Microsoft MVP - Excel

"Tom Carlson" <tcarl...@earthlink.net> escreveu na mensagem
news:uHB9fZS0AHA.2324@tkmsftngp02...