Lock cells that have got formulas inside applying a Visual Basic Code that hide or show those cells.

[Diego]

Good day,
I would like to lock cells that have got formulas inside but that is not possible as those cells are hidden or shown through a Visual Basic code and so when I lock those cells and then apply the Visual Basic code to hide or show the same cells the following message appears:

There was an error "1004" at runtime:
You can not assign the Hidden property of the Range class.

Summarizing I would like to lock cells that contains formulas inside, without the message error described above.

Thanks in advanced and I look forward to hearing from you.

 

[webmax]

hi
can u kindly upload the sample file.

 

[Diego]

Sure. Thats the file however its in Spanish. Hope thats not a problem.

 

[p45cal]

There are several ways to solve this one, here's 2:
1. When you protect the sheet, place ticks in the boxes:
Format Rows
Format Columns
or:
2. The use of a vba line like:
Sheets("Definitivo").protect userinterfaceonly:=true

This line need only be executed once and remains active until the file is closed. Re-open the file and it will need to be executed again somewhere.
It allows code to change the protected sheet but not the user.
You could place it in the Workbook_Open event of ThisWorkbook (where you might instead use Hoja5.Protect userinterfaceonly:=true this means the Definitivo sheet name can be changed without affecting the running of the code), or the Worksheet_Activate() of the sheet itself (where you might use Me.Protect userinterfaceonly:=true), or at the beginning of any macro you know will be changing that sheet (and if that macro is in the sheet's own code module you would use Me.Protect userinterfaceonly:=true again).

 

[Diego]

Sorry for the late reply @p45cal
Im apprecited for your help. Thank you very much!!!