Excel - 如果可能，如何锁定单元格而不使用宏 [英] Excel - How to lock cell without using macros if possible

问题描述

我使用宏实现了解决方案，但是如果可以的话我想删除它。所以这里的问题

我在单元格上使用验证规则实现了下拉列表。我希望该单元格是根据同一页上第二个单元格中的值来读取的。

我尝试使用另一个验证锁定，但不允许我。 p>

任何想法？

解决方案

我假设数据验证单元格 A2 ，您正在检查的值位于单元格 A1

当您将单元格 A1 的值更改为Blah Blah时，代码将运行，然后锁定单元格 A2 。在实际运行代码之前，请稍等一下阅读代码中的注释。

代码必须粘贴在工作表代码区中。

  Option Explicit 
 
 Private Sub Worksheet_Change（ByVal Target As Range）
 
如果Target.Cells .Count> 1然后退出Sub 
 
 Application.EnableEvents = False 
 
 Dim mypassword As String，StringToCheck As String 
 
错误GoTo Whoa 
 
'~~>在此处更改密码以保护/取消保护工作表
 mypassword =password
 
'~~>将其更改为要比较的相关字符串
 StringToCheck =Blah Blah
 
如果不相交（目标，范围（A1））没有，然后
 ~~>检查单元格值
如果Target.Value = StringToCheck然后
'~~>取消保护工作表
 ActiveSheet.Unprotect mypassword 
 
'~~>锁定单元格
范围（A2）。Locked = True 
 
'~~>重新保护工作表
 ActiveSheet.Protect mypassword 
如果
结束If 
 
 LetsContinue：
 Application.EnableEvents = True 
退出子
哇：
 MsgBox Err.Description 
 Resume LetsContinue 
 End Sub 
  

I have solution implemented using the macro but I want remove it if I can. So here the issue

I have drop-down implemented using validation rule on a cell. I want that cell to be readonly depending on the value in second cell on same sheet.

I tried locking the using another validation but it don't allow me.

Any Idea?

解决方案

I am assuming that the data validation is in cell A2 and the value which you are checking is in Cell A1

When you change the value of cell A1 to "Blah Blah" the code will run and then lock cell A2. Please take a moment to read through the comments in the code before you actually run it.

The code has to be pasted in the worksheet code area.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub

    Application.EnableEvents = False

    Dim mypassword As String, StringToCheck As String

    On Error GoTo Whoa

    '~~> Change the password here to protect/unprotect the sheet
    mypassword = "password"

    '~~> Change it to the relevant string with which you want to compare
    StringToCheck = "Blah Blah"

    If Not Intersect(Target, Range("A1")) Is Nothing Then
        '~~> Check for the cell value
        If Target.Value = StringToCheck Then
            '~~> Un-Protect the sheet
            ActiveSheet.Unprotect mypassword

            '~~> Lock the cell
            Range("A2").Locked = True

            '~~> Re-Protect the sheet
            ActiveSheet.Protect mypassword
        End If
    End If

LetsContinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

这篇关于Excel - 如果可能，如何锁定单元格而不使用宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！