• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Hiding Formulas in a unprotected sheet

I have made a attendance sheet which i'll have to distribute in many departments. I fear that my adversaries (who are everywhere) may try to manipulate the formulas. My Boss says that i do not have to give a protected sheet as some features in the sheet ( Like Grouping & UN-grouping of Columns) does not work when the sheet is locked. So, please help me here. I have too many enemies in our firm. I'm sure someone at some time will surely try something to spoil my name.
Thank You.
 
Excel Enthusiast
Without even a sample file ... a challenge
or
That file which You distribute in many departments --- delete all formulas away!
and
ONLY You have the real version, to which You'll copy datas from others playground-file to Your version, which would use formulas.
 
Maybe with something like this? (without a sample)
Code:
Sub Protect_All_Formula_cells()
On Error Resume Next
Application.ScreenUpdating = False
For i = 1 To Sheets.Count
With Sheets(i)
      .Unprotect Password:="password"
    With .Cells
        .Locked = False
        .FormulaHidden = False
    End With
    With .Cells.SpecialCells(xlCellTypeFormulas, 23)
        .Locked = True
        .FormulaHidden = False
    End With
    .Protect Password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True, _
    AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, _
    AllowInsertingHyperlinks:=True, AllowFiltering:=True
    End With
Next i
Application.ScreenUpdating = True
End Sub
Change password with your password.
Don't forget to protect the VBA code.
 
Maybe with something like this? (without a sample)
Code:
Sub Protect_All_Formula_cells()
On Error Resume Next
Application.ScreenUpdating = False
For i = 1 To Sheets.Count
With Sheets(i)
      .Unprotect Password:="password"
    With .Cells
        .Locked = False
        .FormulaHidden = False
    End With
    With .Cells.SpecialCells(xlCellTypeFormulas, 23)
        .Locked = True
        .FormulaHidden = False
    End With
    .Protect Password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True, _
    AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, _
    AllowInsertingHyperlinks:=True, AllowFiltering:=True
    End With
Next i
Application.ScreenUpdating = True
End Sub
Change password with your password.
Don't forget to protect the VBA code.

Well BELLEKE Hi! I am attaching the file which i had referred to in my thread. First of all, as i'm a novice i have worked very hard to make this file. I work in a Rice Sheller. There are many Rice Sheller in our area. I am afraid that my work might be copied and distributed in the neighboring sheller, which my boss has said should not happen.
So now what i actually need is that no one must be able to see the formulas that I've used and most importantly the whole file or its sheets should not be copied or moved.
CELL B3:B52 , E3:AI52, AQ3:AQ52 only these cells shall be editable in the first sheet.

Your response will be greatly appreciated.
 

Attachments

  • Employee-Attendance-Sheet.xlsm
    39 KB · Views: 5
Maybe with something like this? (without a sample)
Code:
Sub Protect_All_Formula_cells()
On Error Resume Next
Application.ScreenUpdating = False
For i = 1 To Sheets.Count
With Sheets(i)
      .Unprotect Password:="password"
    With .Cells
        .Locked = False
        .FormulaHidden = False
    End With
    With .Cells.SpecialCells(xlCellTypeFormulas, 23)
        .Locked = True
        .FormulaHidden = False
    End With
    .Protect Password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True, _
    AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, _
    AllowInsertingHyperlinks:=True, AllowFiltering:=True
    End With
Next i
Application.ScreenUpdating = True
End Sub
Change password with your password.
Don't forget to protect the VBA code.

I copied the code and pasted it. But it is not working. The formulas are still visible.
 
Can you Try this macro?

Code:
Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
Me.Unprotect "MY_Pass"
Me.Cells.Locked = False
Dim My_SELECT As Range
Dim CEL As Range
Set My_SELECT = Intersect(Range("B3:B52 , E3:AI52, AQ3:AQ52"), Selection)
If My_SELECT Is Nothing Then GoTo End_me
With Me
    For Each CEL In My_SELECT
        If CEL.HasFormula Then
           With CEL
            .Locked = True
            .FormulaHidden = True
            .Interior.ColorIndex = 6
           End With
         End If
     Next
End With
End_me:
  Me.Protect "MY_Pass"
Application.EnableEvents = True

End Sub
 
Can you Try this macro?

Code:
Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
Me.Unprotect "MY_Pass"
Me.Cells.Locked = False
Dim My_SELECT As Range
Dim CEL As Range
Set My_SELECT = Intersect(Range("B3:B52 , E3:AI52, AQ3:AQ52"), Selection)
If My_SELECT Is Nothing Then GoTo End_me
With Me
    For Each CEL In My_SELECT
        If CEL.HasFormula Then
           With CEL
            .Locked = True
            .FormulaHidden = True
            .Interior.ColorIndex = 6
           End With
         End If
     Next
End With
End_me:
  Me.Protect "MY_Pass"
Application.EnableEvents = True

End Sub

No Sir, Sorry sir but this stopped my autocomplete drop down from working and the formulas are also still visible.
But thanks anyway.
 
Can you Try this macro?

Code:
Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
Me.Unprotect "MY_Pass"
Me.Cells.Locked = False
Dim My_SELECT As Range
Dim CEL As Range
Set My_SELECT = Intersect(Range("B3:B52 , E3:AI52, AQ3:AQ52"), Selection)
If My_SELECT Is Nothing Then GoTo End_me
With Me
    For Each CEL In My_SELECT
        If CEL.HasFormula Then
           With CEL
            .Locked = True
            .FormulaHidden = True
            .Interior.ColorIndex = 6
           End With
         End If
     Next
End With
End_me:
  Me.Protect "MY_Pass"
Application.EnableEvents = True

End Sub

Me.Protect "MY_Pass" SHALL I PUT MY PASSWORD HERE
Application.EnableEvents = True
 
Can you Try this macro?

Code:
Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
Me.Unprotect "MY_Pass"
Me.Cells.Locked = False
Dim My_SELECT As Range
Dim CEL As Range
Set My_SELECT = Intersect(Range("B3:B52 , E3:AI52, AQ3:AQ52"), Selection)
If My_SELECT Is Nothing Then GoTo End_me
With Me
    For Each CEL In My_SELECT
        If CEL.HasFormula Then
           With CEL
            .Locked = True
            .FormulaHidden = True
            .Interior.ColorIndex = 6
           End With
         End If
     Next
End With
End_me:
  Me.Protect "MY_Pass"
Application.EnableEvents = True

End Sub

Please use the attached file named FINAL ATT SHEET that i've just attached
 
FINAL ATT SHEET
I get 'compile errors on a hidden sheet'.

Whilst utilities to select and lock the relevant cells are useful, ultimately they rely upon protection being applied to the workbook. To gain a measure of security, your boss has to give way on the protection issue. It may be that more operations are possible on a protected sheet than you realise. For example, the grouping and hiding of columns is possible, provided the selection of locked cells is permitted (I also allowed the insertion/deletion of columns and the formatting of columns).

The unprotected workbook that I posted, works on the basis that "If cracking the workbook takes more knowledge or time than writing it from scratch, the security level is sufficient". In case anyone looked at the workbook, all the formulas are held as the 'refers to' property of a Name. The Names are then hidden using
Code:
Name.Visible = FALSE
something that can only be done from code since there is no user interface control for this functionality. I know of an instance where this device has held a bank IT department at bay because they were looking for hidden code modules that did not exist.

A further thought: Make sure you are not sharing anything here that you wish to keep secret.
 
I get 'compile errors on a hidden sheet'.

Whilst utilities to select and lock the relevant cells are useful, ultimately they rely upon protection being applied to the workbook. To gain a measure of security, your boss has to give way on the protection issue. It may be that more operations are possible on a protected sheet than you realise. For example, the grouping and hiding of columns is possible, provided the selection of locked cells is permitted (I also allowed the insertion/deletion of columns and the formatting of columns).

The unprotected workbook that I posted, works on the basis that "If cracking the workbook takes more knowledge or time than writing it from scratch, the security level is sufficient". In case anyone looked at the workbook, all the formulas are held as the 'refers to' property of a Name. The Names are then hidden using
Code:
Name.Visible = FALSE
something that can only be done from code since there is no user interface control for this functionality. I know of an instance where this device has held a bank IT department at bay because they were looking for hidden code modules that did not exist.

A further thought: Make sure you are not sharing anything here that you wish to keep secret.
[/Q

Thank You so much PETER. Will try by protecting sheet.
 
Back
Top