Excel VBA - 关于单元格选择的Unprotect工作表（单击，不是双击）

I'm trying to create a macro in Excel 2010 that will unprotect my worksheet when clicking (not double-clicking) a cell in a specific range, at the contrary, if I click anywhere else, I'd like to protect my worksheet.

我正在尝试在Excel 2010中创建一个宏，在单击（而不是双击）特定范围内的单元格时取消保护我的工作表，相反，如果我点击其他任何地方，我想保护我的工作表。

Sorry, I'm not a VBA expert.

对不起，我不是VBA专家。

I came up with this code:

我想出了这段代码：

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("E9:E22,I9:I21,N9:N20,Q9:Q14")) Is Nothing Then

        ActiveSheet.Unprotect Password:="abc"

    ElseIf Intersect(Target, Range("E9:E22,I9:I21,N9:N20,Q9:Q14")) Is Nothing Then

        ActiveSheet.Protect _
        Password:="abc", _
        UserInterfaceOnly:=True, _
        AllowFiltering:=True, _
        AllowSorting:=True, _
        AllowUsingPivotTables:=True

    End If
End Sub

This code is perfectly working if, obviously, I double-click the cell, but I was wondering if there is any way to make it works if I only select (one click) a cell?

如果，显然，我双击单元格，这个代码是完美的工作，但我想知道如果我只选择（一次单击）一个单元格，是否有任何方法可以使它工作？

I tried using ActiveCell instead of Target but with no success.

我尝试使用ActiveCell而不是Target但没有成功。

Thank you very much, Stefano

非常感谢，Stefano

1 个解决方案

#1

This is due to you are using BeforeDoubleClick Event. Try using Worksheet_SelectionChange instead.

这是因为您正在使用BeforeDoubleClick事件。请尝试使用Worksheet_SelectionChange。