code for hiding formulas
 

I have one code from community which hides the cells and protect with
password. But I want some one to help me with a code which will hide only
formulas in the workbook including all worksheets as and when a workbook
opens.As I am new VBA the code that can be copied strainght away may please
be provided so that I can use.
Thank you in advance
srinivasan

code for hiding formulas
 

Test this/perform these actions in a test workbook - that has formulas on a
least some of the sheets.

Private Sub Workbook_Open()
Dim rng as Range
Dim sh as Worksheet
On Error Resume Next
for each sh in ThisWorkbook.Worksheets
sh.Activate
sh.Unprotect Password:="ABCD"
set rng = sh.UsedRange.SpecialCells(xlFormulas)
rng.Locked = True
rng.FormulaHidden = True
sh.Protect Password:="ABCD"
Next
End Sub

Go to the VBE (alt+F11) and find your workbook in the project explorer.
Right click on the ThisWorkbook entry for your workbook and select view
code.

Paste in code like the above. Change the password to the correct password.

Now go back to Excel and save the workbook. Close the workbook and test it.

When you satisfied it is doing what you want, then you can do the same in
your workbook.

Just note that if the user disables macros or holds down the shift key when
opening your workbook, this macro will never run.

--
Regards,
Tom Ogilvy


"srinivasan.V" wrote in message
...
I have one code from community which hides the cells and protect with
password. But I want some one to help me with a code which will hide only
formulas in the workbook including all worksheets as and when a workbook
opens.As I am new VBA the code that can be copied strainght away may
please
be provided so that I can use.
Thank you in advance
srinivasan

code for hiding formulas
 

Dear Mr Tom,
Thanks for your immediate response and for the detailed enlightening. It
works fine. But it locks entire worksheet and another user can not enter any
data in it. So can it be modified to hide formulas in particular cells only
that are colored pale blue so that I can set the cells accordingly.

code for hiding formulas
 

Private Sub Workbook_Open()
Dim rng as Range
Dim sh as Worksheet
On Error Resume Next
for each sh in ThisWorkbook.Worksheets
sh.Activate
sh.Unprotect Password:="ABCD"
set rng = sh.UsedRange.SpecialCells(xlFormulas)
for each cell in rng
if cell.interior.colorIndex = 8 then 'turquoise
cell.Locked = True
cell.FormulaHidden = True
end if
Next
sh.Protect Password:="ABCD"
Next
End Sub

change the 8 to the colorindex that you are using to mark the cells.

--
Regards,
Tom Ogilvy


"srinivasan" wrote in message
...
Dear Mr Tom,
Thanks for your immediate response and for the detailed enlightening. It
works fine. But it locks entire worksheet and another user can not enter
any
data in it. So can it be modified to hide formulas in particular cells
only
that are colored pale blue so that I can set the cells accordingly.

code for hiding formulas
 

Thanks a lot Mr Tom for your kind help. It works fine.

srinivasan.v