受保护的工作表允许通过复制和粘贴来编辑单元格格式 [英] Protected worksheet allows editing cell format by copy and paste

问题描述

我有一个受保护的工作表.只有某些单元格是可编辑的，用户可以写入其中，但不能像往常一样更改单元格格式.如果他决定从另一个工作表复制并粘贴数据以进行挖掘，则另一个工作表的单元格格式将应用于我的单元格.我希望单元格的值是可编辑的，但其单元格格式绝对不可编辑！我该怎么办?

I have a worksheet which is protected. Only some cells are editable and the user can write into them but cannot change the cell format as usual. If he decides to copy and paste data from another worksheet to mine then the cell formatting of the other worksheet is applied to my cells. I want my cells to be editable in value but their cell format must not be editable at all! How can I do that?

提前谢谢！

马可

推荐答案

我使用此方法是为了仅在用户决定复制并粘贴其格式受保护的单元格时粘贴值:

I used this in order to only paste the values if the user decides to copy and paste in the cells whose format is protected:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    If Application.CutCopyMode = xlCopy Then

        Application.EnableEvents = False

        Application.Undo
        Target.PasteSpecial Paste:=xlPasteValues

        Application.EnableEvents = True

    End If

End Sub

它将撤消所有粘贴到工作表中的操作，然后再次粘贴(仅值，无格式).

It undoes any pastes into the worksheet and pastes it again (only values, no formatting).

这篇关于受保护的工作表允许通过复制和粘贴来编辑单元格格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！