Preventing Overwrite of Cell with Formula

[excel_1982]

Hi All,

Is it possible to prevent overwrite of a cell that contains a formula when new values are copied from another worksheet(other workbook).

Also do note, The worksheet can contain more then one cells that have contain formula and these cells can be non-adjacent.

Any idea!!

[royUK]

can't you use sheet protection?

[excel_1982]

I havent tried that...at the moment i was trying doing

SpecialCells function which didnt work because, it is not possible to copy over non-adjacent cells.

Then if i check all cells one by one it takes ages to complete.

I will give protection a go and see what happens

About the protection. Does the protection allow us to protect cells with formula and prevent their overwrite but allow overwrite of all rest of the cells?

[excel_1982]

        bsht = bBk.Sheets("MISC2")
        to2 = bsht.Range("A1")
        asht = abk.Sheets("MISC2")
        from = asht.Range("A1:IV2000")
        Sheet14.Activate()
        Sheet14.Select()
        Sheet14.Application.Selection.Locked = False
        Sheet14.Application.Selection.FormulaHidden = False
        Sheet14.Application.Selection.SpecialCells(Excel.XlCellType.xlCellTypeFormulas, 23).Select()
        Sheet14.Application.Selection.Locked = True
        Sheet14.Application.Selection.FormulaHidden = False
        Sheet14.Protect()
        from.Copy(to2)

The above should protect the cells but it fails on the last line and says that the cells are protected. Is it possible to protect just the cells which contain formulas?

Anyone any idea

[royUK]

Sheet protection protects the whole sheet. You lock the cells that contain formulas.

It might help to attach a workbook