Excel：在单元格上运行宏 [英] Excel: Run A Macro On Cell Click

问题描述

当用户单击该行中的另一个单元格时，我正在使用以下代码将今天的日期插入到该单元格中。

I am using the following code to insert todays date into a cell when a user clicks onto another cell in that row.

目前，该代码已列出像这样如果用户单击单元格AQ8，然后将日期插入到单元格AS8中。

At the moment the code is set out like so; if a user clicks cell AQ8 then insert date into cell AS8.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Target.Address = "$AQ$8" Then
       Range("AS8").Value = DATE
    End If
End Sub

但是，现在我想稍微更改一下此代码，以便当用户更改任一行中的任何AQ单元格时，随后的AS单元格将日期插入该行的适当单元格。因此，如果单击单元格AQ9，则AS9显示今天的日期，而当他们单击AQ10时，AS10显示今天的日期，依此类推。会将其写为目标行函数吗？如果可以的话，我该怎么写这样的东西？有人可以告诉我如何使它完成我所需要的工作

however, now i want to change this code slightly so that when a user changes any AQ cell in either row then the subsequent AS cell has the date inserted into the appropriate cell for that row. So if cell AQ9 is clicked then AS9 shows todays date and when they click AQ10 then AS10 shows todays date and so on. Would this be written as a target row function? and if so how would i write something like this? Can someone please show me how i could get this to do what i need, thanks in advance

推荐答案

如果您不想要以数字表示列，即您要使用 AQ而不是43，则应执行以下操作：

If you don't want to refer to columns by number, i.e. you want to use "AQ" instead of 43, then the following should work:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = Range("AQ1").Column Then
    Range("AS" & Target.Row).Value = Date
  End If
End Sub

对 AQ1中第1行的引用是虚拟的，因为代码只选择该范围的 .Column 。

The reference to row 1 in AQ1 is a dummy, since the code just selects the .Column of that range.

我使用 Worksheet_Change 是因为您说当用户更改任何AQ单元格时 。如果只希望在用户 点击 时发生这种情况，则可以继续使用 Worksheet_SelectionChange 事件

I'm using Worksheet_Change because you said "when a user changes any AQ cell". If you only want this to happen when a user clicks on a cell, then you can keep using the Worksheet_SelectionChange event.

这篇关于Excel：在单元格上运行宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！