Excel是否具有“On Alert”的VBA就像“On Error”一样吗？ [英] Does Excel have VBA for "On Alert" like it does for "On Error"?

问题描述

我发现一个关于可枢转的弹出框实际上是一个警报框，而不是错误框。 Excel有任何类型的On Alert GoTo，就像On Error GoTo一样：

I have found out that a box popping up in relation to a pivottable is in fact an "alert" box as opposed to an "error" box. Does Excel have any kind of "On Alert GoTo :" like it does for "On Error GoTo :"???

我可以完全禁用警报，但是我会当用户输入的名称不正确时，不能向用户发出警报。这也重新分配了事情，我不想要这个！

I can disable the alert completely, but then I fail to warn users when they enter an incorrect name that isn't found on the pivottable. This also reassigns things and I don't want that!

推荐答案

通过数据验证防止警报

确保用户从预定义列表中提供名称的一种方法是使用 Excel的内置数据验证。您可以让单元格提供自己的基于动态范围的下拉列表。您可以从Excel的数据选项卡和/或使用VBA 。引用是动态的，因为如果参考范围改变，下拉列表将随之更改，如果引用范围是表的列，则列表将根据该列增长和收缩。

One way to ensure that a user supplies a name from a predefined list is to use Excel's built-in data validation. You can have a cell provide its own drop-down list of choices that are based on a dynamic range. You can set this up from Excel's data tab and/or by using VBA. The reference is "dynamic" because, if the reference range changes, the drop-down list will change with it, and if the reference range is a column of a table, then the list will grow and shrink according to that column.

如果验证失败，采取的操作也可以自定义，包括条目消息和错误消息。使用VBA时的一个选项是禁用任何操作，而是使用验证的值属性来确定运行时的操作。验证是在单元格值更改之前触发的，所以我猜测它会继续执行你看到的提醒。

The action to take if validation fails can be customized as well, including an entry message and error message. One option when using VBA is to disable any action and instead use the validation's Value property to determine action at runtime. Validation is triggered before a cell value is changed, so my guess is that it will proceed the alert your seeing.

错误与警报（我们可以使用事件吗？）

关于更一般的问题，关于是否有一个 On Error 相当于警报消息，我不认为有。 VBA没有开始出现恒星错误处理。 On Error 在消息框显示之前处理运行时错误，而不是显示事件，我认为没有任何由消息框显示引起的VBA易访问事件。

Errors vs Alerts (Can we work with an event?)
In regard to the more general question about if there is an On Error equivalent for alert messages, I don't think there is. VBA doesn't have stellar error handling to begin with. On Error handles runtime errors before message box display, not on a display event, and I don't think there are any VBA readily accessible events caused by message box display.

错误与警报（我们可以使用对象吗？）

听起来你发现 Application.DisplayAlerts = False ，我认为这与提醒一样多。正如OP所提及的那样，改变警报是很好的，但仍然以编程方式检查它们。您可以使用 Err 对象来执行此操作，但如果没有警报对象，我不认为您可以使用警报。

Errors vs Alerts (Can we work with an object?)
It sounds like you found Application.DisplayAlerts = False, and I think that's about as much as you can do with alerts. As the OP mentions, it would be nice to turn of alerts but still programatically check them. You can do this with errors by using the Err object, but I don't think you can do it with alerts if there's no alert object.

这篇关于Excel是否具有“On Alert”的VBA就像“On Error”一样吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！