锁定/解锁单元格 VBA

Lock/unlock cells VBA

提问by themolestones

I have been looking for a code to lock all the cells in a sheet but one and I found this code:

我一直在寻找一个代码来锁定工作表中的所有单元格，但我找到了这个代码：

Worksheets("SW").Range("D2").Locked = False
Worksheets("SW").Protect UserInterfaceOnly:=True

it really worked fine until I saved it and closed/opened the file again, then any macros I had were run.

它真的很好用，直到我保存它并再次关闭/打开文件，然后运行我拥有的任何宏。

I guess it is because in that sheet I have different macros and also I have the macro that runs when a cell is changed:

我想这是因为在该工作表中我有不同的宏，而且我还有在单元格更改时运行的宏：

Private Sub Worksheet_Change(ByVal Target As Range)

Do you have any idea how to make it work?

你知道如何让它工作吗？

One more thing, this workbook it will be shared, so I don't know that will affect the macros.

还有一件事，这个工作簿将被共享，所以我不知道这会影响宏。

sorry maybe I was too ambiguous.

对不起，也许我太模棱两可了。

Yes I want to prevent others to change de value of the cells.

是的，我想阻止其他人更改单元格的值。

I want that just one cell could be changed so all the others are changing with the macros.

我希望只能更改一个单元格，以便所有其他单元格都随宏一起更改。

Thanks again for all your help

再次感谢你的帮助

PS: yes Ahmad Al-Mutawa, I want to be impossible to chang the sheet even after the project being saved, closed and opened.

PS：是的，Ahmad Al-Mutawa，即使在项目被保存、关闭和打开后，我也不希望更改工作表。

If you need more info just tell me I'll try to be more specific.

如果您需要更多信息，请告诉我，我会尽量提供更具体的信息。

采纳答案by bonCodigo

Does your file have any macros doing other operations than this lock unlock code you are trying to achieve?

除了您要实现的锁定解锁代码之外，您的文件是否有任何宏执行其他操作？

If so I suggest you having a code in vba. Otherwise you can simply lock the sheets to prevent users from changing somethings or anytime at all without a code.

如果是这样，我建议您在 vba 中有一个代码。否则，您可以简单地锁定工作表以防止用户在没有代码的情况下更改某些内容或随时更改。

do you think this could be better for you than a [code][1]?

你认为这比 [code][1] 更适合你吗？

[ http://office.microsoft.com/en-us/excel-help/lock-or-unlock-specific-areas-of-a-protected-worksheet-HA010096837.aspx][1]

[ http://office.microsoft.com/en-us/excel-help/lock-or-unlock-specific-areas-of-a-protected-worksheet-HA010096837.aspx][1]

Out of curiosity: BY ANY CHANCE are you having an issue not knowing where and how to trigger this code piece?

出于好奇：无论如何，您是否有不知道在何处以及如何触发此代码段的问题？

Edited code:-Your comment is not clear, nonethless I believe you want to use a macro. Assuming you want to protect/lock or unprotect/unlock Sheet1, please try the following. :-

编辑代码：-您的评论不清楚，但我相信您想使用宏。假设您要保护/锁定或取消保护/解锁 Sheet1，请尝试以下操作。:-

Add this code in Workbook open event in thisworkbook:-

在此工作簿的工作簿打开事件中添加此代码：-

Private Sub Workbook_Open()
  Dim shtSheet As Worksheet
  Dim strPassword As String

    strPassword = "stack"
    Set shtSheet = Worksheets("Sheet1")
    shtSheet.Protect Password:=strPassword, UserInterfaceOnly:=True
End Sub

Next add the following in your module. And call it after you finish running your macro.

接下来在您的模块中添加以下内容。并在运行完宏后调用它。

 Sub ProtectSheet()
  Dim shtSheet As Worksheet
  Dim strPassword As String

     strPassword = "stack"
     Set shtSheet = Worksheets("Sheet1")
     shtSheet.Protect strPassword
 End Sub