Vba to lock cells with formula in a table

Marcia · #1 07-27-2024, 08:39 PM

Hi all.
I have copied a code that lock cells with formula in a workbook. I would like the code to lock the cells with formula in table "tAbstract" only, not the whole workbook. Would someone please edit the code?
Also, I would like the message "Ooooops formula na" pops up when/if the user accidentally goes to a protected cell.
Thank you for the usual help.

Code:

Sub ProtectFormulaCells()
Dim sh As Worksheet
On Error Resume Next
For Each sh In ActiveWorkbook.Sheets
    With sh
        .Unprotect
        .Cells.Locked = False
        .Cells.SpecialCells(xlCellTypeFormulas).Locked = True
        .Protect
    End With
Next sh
End Sub

p45cal · #2 07-28-2024, 02:31 AM

Quote:

Originally Posted by Marcia

to lock the cells with formula in table "tAbstract" only, not the whole workbook.

Could you just confirm, tAbstract is (the name of) a proper Excel table (listObject) on a worksheet, and not a worksheet name?

Marcia · #3 07-28-2024, 06:39 PM

Quote:

Originally Posted by p45cal

Could you just confirm, tAbstract is (the name of) a proper Excel table (listObject) on a worksheet, and not a worksheet name?

tAbstract is a proper Excel table (listObject).
Thank you.

p45cal · #4 07-29-2024, 05:13 AM

This will work regardless of which sheet you're on as long as the workbook you want it to work in is the active workbook:

Code:

Sub ProtectFormulaCellsInTable_tAbstract()
With Range("tAbstract")
  .Parent.Unprotect
  .SpecialCells(xlCellTypeFormulas).Locked = True
  .Parent.Protect
End With
End Sub

Changing the message when someone tries to change a locked cell on a protected sheet is not possible (afaik) but something else can be done, see:
custom error message in popup window for protected cell? | MrExcel Message Board

…but is it worth it?

Marcia · #5 07-31-2024, 10:11 PM

Thank you for the usual quick help and the link. I am using the code that you wrote as is.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Table Help: lock table structure and collapse/expand parts of table	bsafra1	Word	0	11-06-2018 03:01 AM
Lock items in a table	village	Word	2	06-22-2017 07:33 AM
Lock Linked Cells And Unlinked Cells In A Row	paulkaye	Excel	5	07-14-2014 10:20 PM
How to lock only some cells in 2010 ?	spookiepower	Word	4	11-26-2013 02:40 PM
Protect / Lock Cells / Print Set-up?	meggenm	Excel	3	01-26-2012 09:57 PM