如何在电子表格中将单元格设为只读？

I'm populating a spreadsheet with Database values. The cells that I fill data with, I want them to be read-only to the users. How can I do this?

我正在使用数据库值填充电子表格。我填充数据的单元格，我希望它们对用户是只读的。我怎样才能做到这一点？

5 个解决方案

#1

Depends on how you want to select the range. This is just one cell. If you go back to J1 and change the value, you should get prompted.

取决于您希望如何选择范围。这只是一个细胞。如果你回到J1并更改值，你应该得到提示。

Private Sub Worksheet_Change(ByVal Target As Range)
  Range("J1").Select
  Selection.Locked = True
  ActiveSheet.Protect Contents:=True
  Range("K1").Select

End Sub

Cells are not locked until the worksheet is protected. By default all cells are set to Locked, so you'll have to unlock cells you want users to be able to change.

在工作表受到保护之前，单元格不会被锁定。默认情况下，所有单元格都设置为“已锁定”，因此您必须解锁希望用户能够更改的单元格。

#2

In these circumstances I often find the best way is to lock the sheet, but only for the user by using the UserInterfaceOnly argument which still allows unrestricted programmatic interaction with the sheet.

在这些情况下，我经常发现最好的方法是锁定工作表，但仅限用户使用UserInterfaceOnly参数，该参数仍允许与工作表进行无限制的编程交互。

ActiveSheet.ProtectUser InterfaceOnly:=True

There are various other arguments that can be set that will still allow the user to filter, sort etc. should this be required, the Help file has a full list.

有许多其他参数可以设置，仍然允许用户过滤，排序等。如果需要，帮助文件有一个完整的列表。

#3

You can try protecting cells. Or you can code it yourself using a SelectionChange handle event... :

你可以尝试保护细胞。或者您可以使用SelectionChange句柄事件自行编码...：

   Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      If Not Intersect(Selection, Range("A24:A50")) Is Nothing Then
        Range("B1").Select
      End If
    End Sub

#4

In excel you can go to the Protection menu and specify which cells would require a password to modify. You can specify multiple ranges as well.

在excel中，您可以转到“保护”菜单并指定要修改密码的单元格。您也可以指定多个范围。

Hope this is what you were looking for.

希望这是你想要的。

#5

This page offers a small sub to protect a range of cells.

这个页面提供了一个小的sub来保护一系列的细胞。

Excerpt

摘抄

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
    If Not Intersect(Target, Range("H1:H10")) Is Nothing Then 
        Target.Offset(0, 1).Select 
        MsgBox "you may not enter that cell" 
    End If 
End Sub

To protect H1:H10. When the user wants to change a cell within H1:H10 a warning message says that the cell may not be modified, and the neighbor cell in the next column (I) is selected instead.

保护H1：H10。当用户想要更改H1：H10内的单元格时，警告消息表明可能不会修改单元格，而是选择下一列（I）中的相邻单元格。

#1