提示:其它语言是由 Google 机器翻译的。 你可以访问 English 版本。
登录
x
or
x
x
马上登记
x

or

如何在Excel中的单元格值更改时运行宏?

通常,在Excel中,我们可以按F5键或运行按钮来执行VBA代码。 但是,当单元格值发生变化时,您是否尝试过运行特定的宏代码? 本文将介绍一些在Excel中处理这项工作的快速技巧。

使用VBA代码更改特定单元格值时运行或调用宏

使用VBA代码在任何单元格值在范围内更改时运行或调用宏


从多个工作簿中删除所有宏:

Kutools for Excel's 批量删除所有宏 实用程序可以帮助您根据需要从多个工作簿中删除所有宏。 现在下载并免费试用Kutools for Excel!

如果单元格更改3,doc运行宏

Kutools for Excel:比200方便的Excel加载项,可以在60天免费试用。 下载并免费试用现在!


箭头蓝色右泡 使用VBA代码更改特定单元格值时运行或调用宏


要通过更改单元格值来运行宏代码,以下VBA代码可以帮助您,请按照以下方法操作:

1。 如果单元格值发生更改,请右键单击要执行宏的工作表选项卡,然后选择 查看代码 从上下文菜单中,并在打开 Microsoft Visual Basic的应用程序 窗口中,将以下代码复制并粘贴到空白模块中:

VBA代码:单元格值更改时运行宏:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        Call Mymacro
    End If
End Sub

如果单元格更改1,doc运行宏

注意:在上面的代码中, A1 是您想要基于的代码运行的特定单元格, Mymacro 是你想运行的宏名称。 请将它们更改为您的需要。

2。 然后保存并关闭代码窗口,现在,当您输入或更改单元格A1中的值时,将立即触发特定的代码。


箭头蓝色右泡 使用VBA代码在任何单元格值在范围内更改时运行或调用宏

如果您想要在单元格范围内的任何单元格值发生更改时运行或触发宏,则以下代码可能对您有所帮助。

1。 如果单元格值发生更改,请右键单击要执行宏的工作表选项卡,然后选择 查看代码 从上下文菜单中,并在打开 Microsoft Visual Basic的应用程序 窗口中,将以下代码复制并粘贴到空白模块中:

VBA代码:当任何单元格值在一个范围内变化时运行宏:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:B100")) Is Nothing Then
Call Mymacro
End If
End Sub

如果单元格更改2,doc运行宏

注意:在上面的代码中, A1:B100 是您想要基于的代码运行的特定单元格, Mymacro 是你想运行的宏名称。 请将它们更改为您的需要。

2。 然后保存并关闭代码窗口,现在,当您在A1:B100的任何单元格中输入或更改值时,将立即执行特定代码。


相关文章:

如何在Excel中打印之前自动运行宏?

如何在Excel中基于单元格值运行宏?

如何根据从Excel下拉列表中选择的值运行宏?

如何通过单击Excel中的超链接来运行宏?

如何从工作簿中选择工作表时运行宏?


Kutools for Excel - 最佳办公生产力工具提高80%的生产力

  • 重用: 快速插入 复杂的公式,图表 以及你以前用过的任何东西; 加密单元格 密码; 创建邮件列表 并发送电子邮件...
  • 超级方程式酒吧 (轻松编辑多行文字和公式); 阅读布局 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不丢失数据; 分裂细胞含量; 组合重复的行/列...防止重复的细胞; 比较范围...
  • 选择复制或唯一 行; 选择空行 (所有细胞都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择......
  • 精确复制 多个单元格而不更改公式参考; 自动创建参考 多张表; 插入项目符号,复选框等等......
  • 提取文本,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级过滤器 (将过滤方案保存并应用到其他工作表); 高级排序 按月/周/日,频率等; 特殊过滤器 用粗体,斜体......
  • 结合工作簿和工作表; 根据键列合并表; 将数据拆分为多个表格; 批量转换xls,xlsx和PDF...
  • 超过300强大的功能。 支持Office / Excel 2007-2019和365。 支持所有语言。 在您的企业或组织中轻松部署。 全功能60天免费试用。
kte tab 201905

Office选项卡为Office提供选项卡式界面,使您的工作更轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,Publisher,Access,Visio和Project。
  • 在同一窗口的新选项卡中打开并创建多个文档,而不是在新窗口中。
  • 通过50%提高您的工作效率,每天为您减少数百次鼠标点击!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    roberto · 10 months ago
    Hola buenas tardes
    quisiera saber el codigo para que se active una macro cuando cambia el valor de una celda de una columna, pero este valor cambia por formula,sin que el usuario introduzca ningun valor.
  • To post as a guest, your comment is unpublished.
    DrCartwright · 1 years ago
    The macro that you are calling where do you have this located? I have mine in the Modules folder but when I put any value in any cell of the worksheet I get a Compile error saying:
    Expected variable or procedure, not module.

    Please help.
    • To post as a guest, your comment is unpublished.
      skyyang · 11 months ago
      Hi, DrCartwright,
      Sorry for replying to you so late.
      Yes, as you said, the macro code should be located into the Module, and you need to change the code name to your own name as following screenshot shown:
  • To post as a guest, your comment is unpublished.
    Kevin · 1 years ago
    Hey, useful code. I was thinking if it was possible to insert a ring around the cells that are changed as they are changed? And reset the circles every Monday ?
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, Kevin,
      Here is no idea for solving your problem, if you have any good solution, please comment here.
  • To post as a guest, your comment is unpublished.
    Tom · 1 years ago
    Worked great for me! My dilemma is that I want it to be a relative reference macro and there is a difference between hitting enter to save the entry and delete to clear the cell.
  • To post as a guest, your comment is unpublished.
    Andrew Cothliff · 1 years ago
    This worked first time for me using data validation list which displays text based on the list selection.
  • To post as a guest, your comment is unpublished.
    Jach · 2 years ago
    Yeah the description says it should trigger when value changes, but the second code says it's when the value is nothing, and the first one doesn't trigger when a value changes either, but if I press F5 when looking at the code, it wants to trigger MyMacro, but doesn't seem to react to value changes as much as going from nothing to something or opposite.
  • To post as a guest, your comment is unpublished.
    Wayne Hyde · 2 years ago
    This module is not working for me. It says when the VALUE of the cell changes it will execute the code. I find if I TYPE into the cell it works, but if the value of tthe cell is change by a formula, for example a Counta, then it does not execute. Do you have a solution to execute a macro when the VALUE changes through a formula?
    Thanks,
    Wayne