单元格值更改事件和运行连续宏 [英] Cell Value Change Event and Running a Continuous Macro

问题描述

我希望我的宏在Sheet1打开时自动运行，并且在列B中的任何下拉菜单中改变一个值。假设我可以编写几个事件监听器包装器，例如：

 '伪代码
当Sheet1打开时
列B中的下拉列表值更改
调用更新
结束当
结束虽然
  

我在网上找到了几个链接，但我不太明白在这些链接中，他们有代码指向目标。目标是命名范围吗？我没有运气实施这些。我认为这些链接可以解决我的问题。

http：// www。 mrexcel.com/forum/excel-questions/95341-running-macro-continuously.html

http://msdn.microsoft.com/en-us/library/office/ff839775（v = office.15）.aspx

谢谢

解决方案

一个很好的问题！ / p>

您可以使用工作表事件宏：

  Private Sub Worksheet_Change设定rB =范围（B：B）。 b $ b Else 
 Application.EnableEvents = False 
调用更新
 Application.EnableEvents = True 
如果
 End Sub 
  

因为它是工作表代码，它很容易安装和自动使用：

1. 右键单击Excel窗口底部附近的选项卡名称
2. 
   
3. 选择查看代码 - 这将打开一个VBE窗口


4. 将东西粘贴并关闭VBE窗口

如果您有任何疑问，请先试用试用版。

如果保存工作簿，宏将与它一起保存。
如果您在2003年之后使用Excel版本，则必须将
保存为.xlsm而不是.xlsx

要删除宏：

1. 打开上面的VBE窗口


2. 清除代码


3. 关闭VBE窗口

要了解有关宏的更多信息，请参阅：

http://www.mvps.org/dmcritchie/ excel / getstarted.htm

和

http://msdn.microsoft.com/en-us/library/ee814735（v = office .14）.aspx

要了解有关事件宏（工作表代码）的更多信息，请参阅：

http://www.mvps.org/dmcritchie/excel/event.htm

必须启用宏才能使其工作！

I want my macro to run automatically when Sheet1 is open AND a value is changed in any of the drop down menus in Column B. I assume I can write a couple of event listener wrappers such as:

' pseudocode    
While Sheet1 is open
        When a dropdown value is changed in column B
            Call Update
        End When
End While

I've found a few links online, but I don't quite understand. In these links, they have code referring to Target. Is Target a named range? I haven't had any luck implementing these. I'm thinking these links could have an answer to my problem.
http://www.mrexcel.com/forum/excel-questions/95341-running-macro-continuously.html
http://msdn.microsoft.com/en-us/library/office/ff839775(v=office.15).aspx

Thanks

解决方案

A very good question!

You can use a worksheet event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rB As Range
    Set rB = Range("B:B").Cells.SpecialCells(xlCellTypeAllValidation)
    If Intersect(Target, rB) Is Nothing Then
    Else
        Application.EnableEvents = False
            Call Update
        Application.EnableEvents = True
    End If
End Sub

Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!

这篇关于单元格值更改事件和运行连续宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！