• 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.

Formula lock

Status
Not open for further replies.
C

cubexparts

New Member
Good day everyone
Can you please assist by providing a macro to lock and hide all formulas in a workbook (all sheets). Must still be able to use the external link updates.

Thank you very much in advance

Best regards
Jan
 

cubexparts​

What do You mean with to lock?
... or do You mean to set Calculation option to manual?
to hide ... can You protect Your sheets/book?
 

cubexparts​

I asked three questions ...
Maybe You read the 1st question ...
Did You read those 2nd and 3rd questions?
... did You try those two options as I asked?
 
cubexparts said:
Good day everyone
Can you please assist by providing a macro to lock and hide all formulas in a workbook (all sheets). Must still be able to use the external link updates.

Thank you very much in advance

Best regards
Jan
Click to expand...
 
You can use the following VBA macro to lock and hide all formulas in a workbook, while still allowing external link updates. Please follow these steps:

1. Press `ALT + F11` to open the Visual Basic for Applications (VBA) editor.
2. Insert a new module by right-clicking on any item in the left-hand explorer and choosing `Insert` -> `Module`.
3. Copy and paste the following VBA code into the module:

Code: 
Sub Test()

    Dim ws As Worksheet

    Dim cell As Range

   

    ' Loop through all worksheets in the workbook

    For Each ws In ThisWorkbook.Worksheets

        ' Unprotect the sheet in case it's protected

        ws.Unprotect

       

        ' Loop through all cells with formulas

        For Each cell In ws.UsedRange.SpecialCells(xlCellTypeFormulas)

            ' Lock and hide the formulas

            cell.Locked = True

            cell.FormulaHidden = True

        Next cell

       

        ' Protect the sheet with options to allow external link updates

        ws.Protect Password:="YourPassword", _

            UserInterfaceOnly:=True, _

            AllowFiltering:=True, _

            AllowSorting:=True, _

            AllowUsingPivotTables:=True

    Next ws

End Sub

4. Replace `"YourPassword"` in the code with the password you want to use for sheet protection.

5. Run the macro by pressing `F5` or by closing the VBA editor and running it from the "Macros" menu in Excel.

This macro will lock and hide all formulas in each worksheet of the workbook while allowing external link updates. Remember to save your workbook after running the macro.
 
Compile errors often occur due to syntax issues or missing references. In this case, make sure you have the correct references enabled. In the VBA editor, go to Tools > References and check if "Microsoft Excel xx.x Object Library" is selected, where "xx.x" corresponds to your Excel version.

If the issue persists, please share the specific error message you're getting so I can provide more targeted assistance.
 
Hello Cube,

If references in the VBA editor are grayed out, it might indicate a missing or corrupted reference. Try these steps:

1. Open the VBA editor.
2. Go to "Tools" and select "References."
3. Look for any references marked as "MISSING" and uncheck them.
4. Browse and select the correct references for your project.
5. Click "OK" to save changes.

If the issue persists, ensure your workbook is not in compatibility mode and that there are no macro security issues. Additionally, consider repairing or reinstalling Microsoft Office.
 
Hey

comeup with more details what your trying to achieve and steps to be followed and may be the errors you are currently facing to help you.
 
It's nothing to do with references. You just need to delete the blank lines from in between the code lines.
Code: 
        ws.Protect Password:="YourPassword", _
            UserInterfaceOnly:=True, _
            AllowFiltering:=True, _
            AllowSorting:=True, _
            AllowUsingPivotTables:=True
 
How to "remove the password" if one wants to make additional changes to the formulas and cells for the above code, appreciate your feedback. Thankyou
 

AGYANI​

Your ... how to remove .. ?
... by writing needed password there.
You should open a new thread if You have something to ask.
This thread belongs to other member.
This thread is closed now.
 
Status
Not open for further replies.
Back
Top