当用户向单元格中输入触发器时执行子程序 [英] Execute a subroutine when a user enters a trigger into a cell

问题描述

Excel中的示例数据：

  ABC 
 1 9 5 
 2 4 y 3 
 3 1 9 
 4 66 4 
 5 5 9 
  

想要做的是当我在列B中输入 Y 时，我想要 somestuff 执行。

1. 我不认为如果Active.Cell = Y 将在这里工作，因为当我输入 Y ，然后按Enter键， active.cell 不会是刚刚输入的 Y in。



2. 循环通过列B将无法正常工作，因为

   
   
   

   a。在列
   
   

   b中将有多个 Y 。我需要在单元格中输入 Y 后执行 somestuff 。


<你可以建议我应该尝试什么？

解决方案

由于siddarth的建议，Worksheet_change（）是您正在寻找的。这是您如何实现您的任务，没有任何泄漏。将此代码粘贴到正在编辑y值的工作表中。

  Private Sub Worksheet_Change（ByVal Target As Range）
 
 Dim MyRng As Range 
 Set MyRng = Range（B：B）
 
 Dim PieRng As Range 
'相交将确保您当前的单元格位于列B 
设置PieRng =相交（Target，MyRng）
 
'如果条件确保触发器代码只有一个单元格在Col B上编辑并且是'y / Y'。 
如果Target.Columns.Count = 1和Target.Rows.Count = 1然后
如果不是PieRng不是和LCase（Target.Text）=y然后
'做我的东西这里当y / Y输入当前工作表的B列
 MsgBox你输入& Target.Value& in Col B
 End If 
 End If 
 
 
 End Sub 
  

让我们知道它是否失败...




Example data in Excel:

   A    B    C  
1  9         5  
2  4    y    3  
3  1         9  
4  66        4  
5  5         9  

What I want to do is when I enter Y in Column B, I want "somestuff" to execute.

1. I don't think If Active.Cell = Y will work here, because when I enter Y and press enter, the active.cell will not be the one I just entered Y in.

2. Looping through Column B will not work because

   a. There will be multiple Ys in the column

   b. I need to execute "somestuff" right after entering a Y into a cell.

Could you please suggest what should I try?

解决方案

As siddarth suggested, Worksheet_change() is what you are looking for. Here is how you may achieve your task without any leaks. Paste this code inside the sheet in which you are editing the y value.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyRng As Range
Set MyRng = Range("B:B")

Dim PieRng As Range
'Intersect will ensure your current cell lies on column B
Set PieRng = Intersect(Target, MyRng)

'if conditions to ensure trigger code only one cell edited on Col B and is 'y/Y'.
If Target.Columns.Count = 1 And Target.Rows.Count = 1 Then
    If Not PieRng Is Nothing And LCase(Target.Text) = "y" Then
       'Do my stuff here when y / Y are entered in Column B of current sheet
       MsgBox "You entered " & Target.Value & " in Col B"
    End If
End If


End Sub

Let us know if it fails...

这篇关于当用户向单元格中输入触发器时执行子程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！