如何在Excel中实现可重置、可覆盖、有默认值的单元格数值？

Question

>>问题概述

我想在Excel中实现一个可重置、可覆盖、默认单元格值。我的意思是，当第二个单元格更新时，有一个单元格会恢复到由查找公式依赖的第二个单元格的“默认”值。用户还可以将不同的值写入原始单元格，直到下次更新第二个单元格为止。

---

>>主体和详细信息

好的，这里是情况；此快照是多个工作表数据存储库的相关区域。感兴趣的两个单元格以绿色突出显示，最高可见行为第1行。

• 项目搜索单元格接受各种单词或短语输入，并具有数据验证以确保只有有效输入是可能的。数据验证来自可能输入的按字母顺序排列的列表，并且该单元格具有下拉列表选项（因此右侧有小箭头）。

• 堆栈单元格使用以下公式进行项目搜索单元格的输入...

  =IF(COUNTIF(C3:F315,J6),VLOOKUP(J6,C3:F315,4,FALSE),"~")
  

  ...其中J6是项目搜索单元格，范围C3:F315是同一工作表上一个查找表的相关部分。

  

  现在，这是我想在 Stack 单元格中发生的事情...

  • 当前功能：
    • 当向项目搜索单元格中输入无效输入时，会显示波浪线而不是数字。
    • 输入有效输入时，在单元格中显示来自查找表的相关数字。 购买和销售单元格也以相同的方式更新。
  • 期望的附加功能：
    • 在第一种情况下，不能覆盖波浪线。
    • 在第二种情况下，“默认”数字可以被输入到 Stack 单元格中覆盖。
    • 当输入新输入（或仅再次输入相同输入）到项目搜索单元格时，然后再次显示默认数字（或波浪线）。
  • 愿望清单（非必需）：
    • 是否有复选框（或类似的东西;例如相邻单元格中的是/否输入），如果选中，则表示 Stack 单元格中显示的数字不会受到从查找表中读取的任何新“默认值”的影响/更改。该数字仍然可以通过手动输入新数字进行修改。
    • 项目搜索单元格目前具有下拉字母排序列表，其中包含其所有可能的有效数据输入。是否有一种方法可以使用相同的列表将自动完成功能添加到单元格中？也许有点像Google搜索引擎，下拉列表会随着您键入而出现，并且填充该列表的项目将继续限制为包含您到目前为止键入的（子）字符串的项目。

  注意：无论在 Stack 单元格中显示什么值，其他单元格的公式都必须能够读取；即其值将成为购买和销售单元格的比率，在该单元格中显示的值和 Stack 单元格的查找值之间。

  这有任何程度的可能吗？最好（但不仅仅）不需要使用宏。该工作簿旨在分发给其他人，其中大部分被锁定和保护以避免对核心数据进行任何更改。

  谢谢您的预先。

  ---

  迄今为止找到的信息：

  ...但并没有完全解决我的问题。

  1. 我可能需要使用多个单元格来实现相同（或类似）的有效功能（一个单元格保存默认值，另一个单元格保存可能由用户输入的值，第三个单元格保存相关的输出值），但这样做看起来不太好看，也不够直观。这个工作簿旨在分发给其他人，其中大部分内容都被锁定和保护。 -此答案不理想。

  2. 在我提出这个问题之前，在我的互联网搜索中，我找到了这个小小的信息。它说如果我想要恢复到默认值是自动的，那么在工作表更改事件过程中使用以下代码：

  Private Sub Worksheet_Change(ByVal Target As Range)
      If Not Intersect(Target, Range("C2")) Is Nothing Then
          If Range("C2").Value = "" Then
              Range("C2").Value = 1234
          End If
      End If
  End Sub
  

  然而，我不完全清楚这是什么意思，也不知道如何操作。
  --C2是其他人示例中使用的名义单元。

  3. 有人提出了一个（可能）类似的问题，并且提供了此答案，涉及使用自定义数字格式。自定义数字格式是否接受当前在Stack单元格中使用的公式？

  ---

  文档上传：

  目前包括所需功能和愿望清单项。
  Item-inary（public）.xlsm - （MediaFire）
  2012年3月18日，07:40 UCT

  目前包括所需功能和愿望清单项“1”。
  Item-inary（public）.xlsm - （Mediafire）
  2012年3月20日，19:50 UCT

  ---

  >>编辑＃1：

  这是我的代码的各个部分：

  在ThisWorkbook中

  Public temp As Integer 'Used to contain Range("M6").Value once CheckBox5 is ticked
  Public warn As Boolean 'True if CheckBox1 is ticked whilst (vVal = "~")
  
  Private Sub Workbook_Open()
      warn = False 'Initialise to False
  End Sub
  

  在 Sheet1（价格表） 中

  Private Sub CheckBox1_Click()
      If OLEObjects("CheckBox1").Object.Value = True Then
          If Range("M6").Value = "~" Then
              warn = True
          Else
              temp = Range("M6").Value
              warn = False
          End If
      End If
  End Sub
  
  
  Private Sub Worksheet_Change(ByVal Target As Range)
      Dim vVal As Variant
  
      On Error GoTo Whoa
  
      vVal = Application.Evaluate("=IF(COUNTIF(C3:F315,J6),VLOOKUP(J6,C3:F315,4,FALSE),""~"")")
  
      '~~> If J6 has been changed, then continue. Otherwise skip.
      If Not Intersect(Target, Range("J6")) Is Nothing Then
          Application.EnableEvents = False
          ActiveSheet.Unprotect ("012370asdf")
  
          If vVal = "~" Then
              Range("M6").Value = "~"
              Range("M6:M7").Locked = True
          Else
              '~~> Check if CheckBox5 is ticked.
              If OLEObjects("CheckBox5").Object.Value = True Then
                  '~~> Checks if CheckBox5 was ticked whilst (vVal = "~")
                  If warn = True Then
                      temp = vVal
                      warn = False 'Reset warn status now that special case is resolved
                  End If
                  Range("M6").Value = temp
              Else
                  Range("M6").Value = vVal
              End If
              Range("M6:M7").Locked = False
          End If
  
          ActiveSheet.Protect ("012370asdf")
          GoTo LetsContinue
      End If
  
      '~~> If M6 has been changed, then continue. Otherwise skip.
      If Not Intersect(Target, Range("M6")) Is Nothing Then
          Application.EnableEvents = False
  
          If OLEObjects("CheckBox5").Object.Value = True Then
              temp = Range("M6").Value
          End If
  
          GoTo LetsContinue
      End If
  
  LetsContinue:
      Application.EnableEvents = True
      Exit Sub
  Whoa:
      MsgBox err.Description
      Resume LetsContinue
  End Sub
  

  这段代码目前还没有包含任何'愿望清单2'的功能，但是其他方面都能够正常运行。

  非常感谢那些提供帮助的人。

- Iakovosian

---

写出如此详细的问题，点个赞！ - Jeremy Harris

你的问题描述非常详细和清晰。如果能够看到实际文件，将会更有助于理解你所需要的细节。+1 - brettdj

快速问题。如果单元格J6有数据验证，那么它如何具有无效输入？ - Siddharth Rout

还有一个建议。在使用 Worksheet_Change 时，一定要始终使用错误处理并将启用事件切换为 False，以避免可能的无限循环 :) - Siddharth Rout

因为我将错误警报设置为“警告”样式。这使用户可以选择继续即使输入无效数据。这是为了那些不理解他们应该输入什么或者不知道确切拼写的特殊人士而设计的，他们不会欣赏自己被非常难以避免的“停止”类型错误警报所骚扰。 - Iakovosian

Answer 1

作为我的早期建议，您正在使用的当前代码应该被写成：

@SiddharthRout：我仍然会上传文件的当前副本供您审阅。我的问题的某些部分已得到回答，但仍有两个项目在我的“愿望清单”中未完成！

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoa

    If Not Intersect(Target, Range("J6")) Is Nothing Then
        Application.EnableEvents = False
        ActiveSheet.Unprotect ("012370asdf")
        If Application.Evaluate("=IF(COUNTIF(C3:F315,J6),VLOOKUP(J6,C3:F315,4,FALSE),""~"")") = "~" Then
            Range("M6").Value = "~"
            Range("M6:M7").Locked = True
        Else
            Range("M6").Formula = "=IF(COUNTIF(C3:F315,J6),VLOOKUP(J6,C3:F315,4,FALSE),""~"")"
            Range("M6:M7").Locked = False
        End If
        ActiveSheet.Protect ("012370asdf")
    End If

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

这也使得多余的单元格N6不再需要使用。

我现在正在查看其余的内容，很快会更新。

更新: 您在愿望清单中的两个请求都已完成。

您的Worksheet_Change事件现在变成了这个样子，以包含愿望清单1 (请参阅附加的快照)。

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim vVal As Variant

    On Error GoTo Whoa

    vVal = Application.Evaluate("=IF(COUNTIF(C3:F315,J6),VLOOKUP(J6,C3:F315,4,FALSE),""~"")")

    If Not Intersect(Target, Range("J6")) Is Nothing Then
        Application.EnableEvents = False

        ActiveSheet.Unprotect ("012370asdf")

        '~~> Check the value of the CheckBox and update cells only if false
        '~~> This is valid for "~" as well i.e if the checkbox is Checked then
        '~~> even "~" remain unchanged. If you don't want this, then move the 
        '~~> below condition inside "ELSE" part :)
        If OLEObjects("Checkbox1").Object.Value = False Then
            If vVal = "~" Then
                Range("M6").Value = "~"
                Range("M6:M7").Locked = True
            Else
                Range("M6").Value = vVal
                Range("M6:M7").Locked = False
            End If
        End If

        ActiveSheet.Protect ("012370asdf")
    End If

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

对于您的第二个愿望清单，我有两个选项。我选择了第二个选项。

1）按照www.ozgrid.com上描述的方法进行操作。

主题：在Excel数据验证列表中自动完成输入

链接：http://www.ozgrid.com/Excel/autocomplete-validation.htm 以及

2）使用控件而不是DV列表。为此，我对列表进行了以下更改：

• 删除J6单元格中的数据验证
• 从名称管理器中为X3:X315的列表命名。我称其为“List”
• 在设计模式下在J6单元格上方放置一个组合框，并将.ListFillRange设置为上述“List”
• 将以下代码添加到工作表代码区域

代码

Private Sub ComboBox1_Click()
    Range("J6").Value = ComboBox1.Value
End Sub

Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
    If KeyCode = 13 Then
        Range("J6").Value = ComboBox1.Value
    End If
End Sub`

现在你的ComboBox将在你在框中输入任何内容时自动完成。

快照

示例文件链接（此链接有效期为7天）

示例文件

希望对你有所帮助

Sid

- Siddharth Rout

---

+1 用于错误处理 + 启用事件 + 让愿望成真 ;) - Pradeep Kumar

直到今天我才有时间回复您。不知为何，您的示例文件在我的电脑上似乎无法像快照中那样工作。首先，组合框似乎没有填充。但是，您在回复中提供的代码以及示例文件中的代码都非常有帮助。我已经尝试了一下，并且生成的代码显示在我的问题下的 EDIT 1: 中。 - Iakovosian

我已经添加了当前错误消息的快照。是的，宏已启用（除非它们还需要在Excel选项之外的某个地方启用）。 - Iakovosian

假设这是指您上传的示例文件，我按照说明操作了 - 但在关闭并重新打开文档后，“ListFillRange”属性被清除了。 - Iakovosian

老实说，那是因为我的愚蠢！进入workbook_open代码并删除所有代码。那个东西正在重置它。我忘记删除了。对此我深表歉意。 - Siddharth Rout

Answer 2

我现在不在电脑旁，所以无法测试，但以下是你需要做的：

你“迄今为止发现的信息”中的第二点是正确的方向，即使你说你不想使用宏。

将Stack单元格的公式移到另一个未被使用的单元格中。锁定此单元格，并将背景和文本颜色设置为相同（以便“隐藏”它）。现在，假设这在O6中。（或者在另一个用户无法访问的工作表上放置此单元格。我经常有一个专门用于这些的隐藏工作表。）

右键单击工作表选项卡，选择查看代码。在新窗口中，双击要运行此代码的工作表名称。

Private Sub Worksheet_Change(ByVal Target As Range)应该是默认出现的函数（并且为空）。

将以下代码放入Worksheet_Change例程中：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Set rng = Intersect(Range("J6"), Target)

    'If J6 has not been changed, then exit.  Otherwise continue.
    If rng Is Nothing Then
        Exit Sub
    Else
        'Replace password with the password that you use to protect the sheet (two places)
        ActiveSheet.Unprotect ("password")
        If Range("O6").Value = "~" Then
            Range("M6").Value = "~"
            Range("M6:M7").Locked = True
        Else
            Range("M6").Value = Range("O6").Value
            'Use M6:M7 here instead of just M6 because cells are merged.
            Range("M6:M7").Locked = False
        End If 
        ActiveSheet.Protect ("password")
    End If
End Sub

- lnafziger

---

谢谢你的回复。我有一些其他编程语言的经验，所以我可以大致理解代码的设计目的（看到你的解决方案的优雅让我感到开心）。我能够按照你的说明操作，但运行时出现错误，这与更改“Range”类的“Locked”属性有关。在快速浏览相关的Excel帮助文件后，我启用了对VBA项目对象模型的访问权限，但似乎这不是答案。也许该属性名称略有不同？我会去查看。我正在运行Excel 2010。 - Iakovosian

刚刚意识到需要将我的实际锁定/解锁密码放在单词的位置！另外，我尝试更改其中一行为Me.Protect("password"), userinterfaceonly:=True，基于我在这里阅读到的内容。我还假设最后一行是打算写成 End Sub。但仍然收到“运行时错误'1004'” 的提示。 - Iakovosian

如果使用userinterfaceonly，则可以完全删除Unprotected/Protect调用（但在需要保护的情况下，必须使用该选项）。我没有使用它的原因是因为你在问题中已经表示过它已经得到了保护，所以你也可以从普通的Excel菜单中进行保护。 - lnafziger

无法设置范围类的 Locked 属性。我无法发现更多关于实际原因的信息。晚安！ - Iakovosian

好的，问题解决了：由于您的单元格已合并，因此在锁定和解锁时必须使用包括所有合并单元格的完整范围。因此，请将M6更改为M6:M7以用于两个锁定命令。 - lnafziger