Excel VBA –更改工作表后运行宏

最新推荐文章于 2023-08-19 11:42:33 发布

翻译最新推荐文章于 2023-08-19 11:42:33 发布 · 938 阅读

1 ·

本内容遵循CC 4.0 BY-SA版权协议

原文链接：https://contexturesblog.com/archives/2010/10/13/excel-vba-macro-runs-when-worksheet-changed/

本文介绍了如何使用Excel VBA创建工作表更改宏，当用户在特定单元格（如客户名称）中输入信息时，自动提醒填写海关表格。特别地，当选定的客户位于加拿大时，会在单元格B5被修改后显示消息。

Are you ready for Spreadsheet Day on October 17th? Maybe you can add a Spreadsheet Day message to all your workbooks, using the technique described in this blog post, so a macro runs when worksheet changed. I'm sure your co-workers would enjoy that!

您准备好在10月17 日的电子表格日吗？也许您可以使用本博客文章中介绍的技术将Spreadsheet Day消息添加到所有工作簿中，以便在工作表更改时运行宏。我相信您的同事会喜欢的！

任务：提醒用户填写海关表格 (The Mission: Remind Users to Fill in Customs Form)

In this example, the workbook has an order form, with a data validation drop down list, where you can select a customer name.

在此示例中，工作簿具有一个订单表单，以及一个数据验证下拉列表，您可以在其中选择一个客户名称。

There are VLOOKUP formulas that pull the address information for the selected customer, to fill in the top of the Order Form sheet. If the customer is located in Canada, you'd like to remind the user to fill in a customs form.

有VLOOKUP公式可为选定客户提取地址信息，以填写“订单表”的顶部。如果客户位于加拿大，则需要提醒用户填写海关表格。

创建一个工作表更改宏 (Create a Worksheet Change Macro)

By using Event code in Excel VBA, you can make a macro run automatically if something happens on the worksheet. In this example, you want the macro to run if there is a change on the worksheet.

通过在Excel VBA中使用事件代码，如果工作表上发生任何事情，您可以使宏自动运行。在本示例中，如果工作表上有更改，您希望宏运行。

The following code will make a message appear when the selected customer is in Canada (cell E7).

当选定的客户在加拿大(单元格E7)时，以下代码将显示一条消息。

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("E7").Value = "Canada" Then
        MsgBox "Please fill in customs form"
    End If
End Sub

仅在特定单元格更改时显示消息 (Show Message Only When Specific Cell is Changed)

On a worksheet where there are multiple cells that can be changed, you might want the message to appear only when a specific cell is changed. In the order form, the message should appear after the customer name is selected, but not every time a product or quantity is entered.

在可以更改多个单元格的工作表上，您可能希望仅在更改特定单元格时显示消息。在订单中，该消息应在选择客户名称之后出现，但并非每次输入产品或数量时都出现。

You can add a couple of lines of code, so it only runs when cell B5 is changed (the customer name).

您可以添加几行代码，因此它仅在更改单元格B5(客户名称)时运行。

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$B$5" Then
    If Range("E7").Value = "Canada" Then
        MsgBox "Please fill in customs form"
    End If
  End If
End Sub

Now the message box will only appear if cell B5 was changed, and the customer is located in Canada.

现在，仅在更改单元格B5并且客户位于加拿大的情况下，才会显示该消息框。

观看Excel工作表事件宏视频 (Watch the Excel Worksheet Event Macro Video)

To see the steps for creating an Excel Worksheet Change Event macro, watch this short Excel video tutorial.

若要查看创建Excel工作表更改事件宏的步骤，请观看此简短的Excel视频教程。

演示地址

下载样本工作簿 (Download the Sample Workbook)

To see the code, and experiment with the Order Form macro, you can download the Order Form Event Code workbook.

要查看代码并尝试使用Order Form宏，您可以下载Order Form事件代码工作簿。

The file is in Excel 2007/2010 macro enabled format, and is zipped. After you unzip and open the file, enable the macros, so you can see the message box. ________

该文件为启用Excel 2007/2010宏的格式，并已压缩。解压缩并打开文件后，启用宏，这样您就可以看到消息框。 ________