Excel VBA：打印前显示一条消息

最新推荐文章于 2024-05-07 07:45:00 发布

culiao2169

最新推荐文章于 2024-05-07 07:45:00 发布

阅读量478

点赞数

文章标签： java python excel linux git

原文链接：https://contexturesblog.com/archives/2009/12/23/excel-vba-show-a-message-before-printing/

版权

A bit of Excel programming can help us save some time and paper. Our goal is to stop people from printing an order form if they forgot to enter a customer name.

一点点的Excel编程可以帮助我们节省一些时间和纸张。我们的目标是阻止人们忘记输入客户名称而打印订单。

Last week, you created a macro to show a message if the customer name was missing in an order form. Today, you'll make a final change to the macro, so it will run automatically when someone tries to print the order form.

上周，您创建了一个宏以在订单表中缺少客户名称时显示一条消息。今天，您将对宏进行最后更改，因此当有人尝试打印订单时，它将自动运行。

Sample File: You can download the Excel order form with last week's message macro. Enable macros when the file opens.

示例文件 ：您可以下载带有上周消息宏的Excel订单。文件打开时启用宏。

创建事件代码 (Create the Event Code)

To create Excel code that runs automatically, you can use Event procedures. Those are macros that run when something specific happens in Excel, such as changing a cell on a worksheet, or printing a worksheet.

若要创建自动运行的Excel代码，可以使用事件过程。这些是在Excel中发生某些特定事件(例如更改工作表上的单元格或打印工作表)时运行的宏。

To open the Visual Basic Explorer (VBE), right-click on the Order Form sheet tab, then click View Code.
若要打开Visual Basic资源管理器(VBE)，请右键单击“订单表单”选项卡，然后单击“查看代码”。

In the VBE, find the order form workbook in the list of projects, and under Microsoft Excel Objects, double-click ThisWorkbook.
在VBE中，在项目列表中找到订单工作簿，然后在“ Microsoft Excel对象”下，双击“ ThisWorkbook”。

At the top left of the Code Window, select Workbook from the drop down list.
在“代码窗口”的左上方，从下拉列表中选择“工作簿”。

An event procedure for Workbook_open is automatically created, but that's not the one we need, so just ignore it. At the top right of the Code Window, select BeforePrint from the drop down list.
Workbook_open的事件过程是自动创建的，但这不是我们需要的过程，因此只需忽略它。在“代码窗口”的右上角，从下拉列表中选择“ BeforePrint”。

The cursor should be flashing between the Sub and End Sub lines for the Workbook_BeforePrint macro. We want to check the order form before someone prints it, so type the following 2 lines of code, to check the name of the active sheet:
光标应该在Workbook_BeforePrint宏的Sub和End Sub行之间闪烁。我们希望在有人打印之前检查订单，因此键入以下两行代码，以检查活动工作表的名称：

Next, copy the code from the CustomerMessage macro, on the Module1, and paste it between the If and End If lines.
接下来，从Module1上的CustomerMessage宏复制代码，并将其粘贴在If和End If行之间。

Now, when someone tries to print or print preview, Excel will check the sheet name.

现在，当有人尝试打印或打印预览时，Excel将检查工作表名称。

If OrderForm is the active sheet, it will check for a customer name in cell B5.
如果OrderForm是活动工作表，它将在单元格B5中检查客户名称。
If the customer name is missing, our warning message will appear.
如果缺少客户名称，则会出现我们的警告消息。

测试事件代码 (Test the Event Code)

To see if the BeforePrint code is working correctly, you can test the code by using Print Preview.

若要查看BeforePrint代码是否正常工作，可以使用“打印预览”来测试代码。

On the Order Form worksheet, delete the Customer Name in cell B5.
在订单工作表上，在单元格B5中删除“客户名称”。
Click the Office Button, point to Print, then click Print Preview.
单击Office按钮，指向打印，然后单击打印预览。

The good new is that the warning message appears, because the Customer Name cell is empty. The bad news is that the Print Preview opens, even though the customer name is missing.

全新的是，由于“客户名称”单元为空，因此出现警告消息。坏消息是，即使缺少客户名称，也会打开“打印预览”。

微调事件代码 (Fine-Tune the Event Code)

You can see "(Cancel as Boolean)" at the end of the first line in the Workbook_BeforePrint macro. Some Excel events have a Cancel option, which allows you to cancel the event, if necessary.

您可以在Workbook_BeforePrint宏的第一行末尾看到“(取消为布尔值)”。某些Excel事件具有“取消”选项，如果需要，您可以使用它取消事件。

We'll add one more line of code, to cancel the printing if the customer name is missing.

如果缺少客户名称，我们将再添加一行代码，以取消打印。

In the Workbook_BeforePrint code, add a blank line, after the MsgBox line.
在Workbook_BeforePrint代码中，在MsgBox行之后添加空白行。
In the blank line, type: Cancel=true
在空白行中，键入：Cancel = true

Then, save the workbook, and try the print preview again.
然后，保存工作簿，然后再次尝试打印预览。

Now the warning message should appear if the customer name is missing, and the print or print preview should be cancelled.

现在，如果缺少客户名称，则应该出现警告消息，并且应该取消打印或打印预览。

If the customer name is filled in, then no message will appear, and the order form will print, or show up in the print preview window. _________________

如果填写了客户名称，则不会出现任何消息，并且定单将打印，或显示在打印预览窗口中。 _________________