使用 VBA 在 Excel 工作表中添加验证列表时出现问题
我有一个 Excel 工作表，其中加载了动态数据结果集。 加载所有数据后，我需要在每行末尾添加“是/否”下拉列表。 我必须动态地执行此操作，因为我事先不知道结果集的大小。 以下代码抛出“应用程序定义或对象定义的错误”：
Dim firstRow As Integer
Dim lastRow As Integer
Dim I As Integer
Dim VOptions As String
VOptions = "1. Yes, 2. No"
firstRow = GetResultRowStart.row + 1
lastRow = GetResultRowStart.End(xlDown).row
For I = firstRow To lastRow
Range("AO" & firstRow & ":AO" & lastRow).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=VOptions
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Options"
.ErrorTitle = ""
.InputMessage = "Click yes or no"
.errorMessage = ""
.ShowInput = True
.ShowError = True
End With
Next I
GetResultRowStart 方法为我提供了在工作表中填充结果数据的起始行。 我也在代码的其他部分使用了这种方法，并且效果很好。 使用消息框进行调试建议在 Range(..).select 语句中抛出错误。
关于此错误原因的任何想法。
I have an excel sheet that is loaded with a dynamic result set of data. I need to add a YES/NO dropdown at the end of each row once all the data is loaded. I have to do this dynamically as I do not know the size of the result set beforehand.
The following code throws an 'Applicaton-defined or object-defined error':
Dim firstRow As Integer
Dim lastRow As Integer
Dim I As Integer
Dim VOptions As String
VOptions = "1. Yes, 2. No"
firstRow = GetResultRowStart.row + 1
lastRow = GetResultRowStart.End(xlDown).row
For I = firstRow To lastRow
Range("AO" & firstRow & ":AO" & lastRow).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=VOptions
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Options"
.ErrorTitle = ""
.InputMessage = "Click yes or no"
.errorMessage = ""
.ShowInput = True
.ShowError = True
End With
Next I
The method GetResultRowStart gives me the row starting which result data is populated in the sheet. I have used this method elsewhere in some other part of the code too and it works perfectly.
Debugging using message boxes suggested error being thrown at the Range(..).select statement.
Any ideas about the cause of this error.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（8）
关于这一点的最终想法：
将工作簿中每个按钮的 SetFocusOnClick 属性设置为 false 似乎已经成功了（至少现在是这样）。
但如果这是一个必需条件，那么当值设置为 true 时，它根本不应该起作用。 然而，有时确实如此。
但这是我找到的可靠解决方案。
Final thoughts on this one :
Setting the SetFocusOnClick property of every button in the workbook to false seems to have done the trick (atleast for now).
But if this is a required condition, it shouldn't have worked at all with the value set as true. However , it sometimes did.
But this is the dependable solution I found.
让我尝试在这里表达我内心的 Spolsky：
如果您引用的范围不在
ActiveSheet上，您应该完全限定该引用。像下面这样的东西应该有效：
Let me try to channel my inner-Spolsky here:
If you're referring to a range not on the
ActiveSheetyou should fully qualify the reference.Something like the following should work:
我使用的解决方案是在 With xx.validation 之前取消对工作表的保护，然后在之后进行保护。 [我不必在 Excel 2000 中执行此操作，并且我认为我不必在 Excel 2003 中执行此操作，直到添加了一个服务包，但不能说 100%。]
The solution i used was to unprotect the worksheet before With xx.validation and then protect if afterwards. [I didn't have to do this in Excel 2000 and I think i didn't have to do it in Excel 2003 until maybe a service pack was added though can't say 100%.]
我也遇到了同样的问题，“自动化错误”。 我所做的就是激活要放入验证列表的工作表，然后错误就消失了。
I also faced the same problem, "automation error". What I did was activate the sheet that I was going to put the validation list in and the error just disappeared.
我刚刚在 Excel 中遇到了非常类似的问题。 我发现当我在直接窗口中运行时以编程方式设置验证下拉列表的代码工作正常，但当从工作表上的按钮调用时则不起作用。 我现在意识到这是因为按钮具有焦点，并且在设置验证之前尝试选择或激活代码中的工作表或单元格似乎无法解决此问题。 但是，我刚刚意识到 Excel 中的按钮有一个“TakefocusOnClick”属性，默认设置为 True。 通过将其设置为 False，按钮永远不会获得焦点，嘿，很快，我设置验证的代码现在可以正常工作了。
这可能不是每个人验证问题的答案，但我希望有人可以从上述内容中受益。
干杯。
I've just experienced a very similar problem in Excel. I found the code to programmatically set the validation dropdown worked fine when I ran it in the immediate window, but didn't work when called from a button on the worksheet. I've now realised it's because the button had the focus, and no amount of trying to select or activate the sheet or cell in the code prior to setting the validation seemed to fix this. However, I've just realised there is a 'TakefocusOnClick' property of buttons in Excel, which is set to True by default. By setting this to False, the button never gets the focus, and hey presto, my code to set validation now works fine.
It might not be the answer to everyone's validation problems, but I hope there might just be somebody who can benefit from the above.
Cheers.
这是“对象‘验证’的方法‘添加’失败”错误的变体。 以下是可能的原因以及解决方法：
受保护的工作表：添加数据验证的工作表无法受到保护，即使即使保护模式为UserInterfaceOnly，要添加验证的单元格也不会锁定。 您必须完全取消对工作表的保护，添加验证，然后重新保护工作表。
工作表单元格区域失去焦点：如果先前单击过的活动工作表上的任何控件（通常是命令按钮）已获得焦点用户，随后调用 Validation.Add 方法时将触发此错误。 （确实，这是真的！）这尤其适用于执行添加验证的代码的任何命令按钮，但它也适用于在执行该代码之前可以单击的工作表上的任何控件。 由于焦点状态与向单元格或范围添加数据验证之间似乎没有合法的联系，因此我认为这是一个 Excel 错误。 以下是解决方法：
A. 防止工作表单元格区域失去焦点：将工作表上所有控件的 TakeFocusOnClick 属性设置为 错误。
B. 将焦点检索到工作表的单元格区域：在 VBA 代码中，在执行 Validation.Add 方法之前，调用选择工作表上任意单元格的方法。 逻辑选择是选择要添加数据验证的单元格或区域，但任何单元格都可以。
This is a variation on the "Method 'Add' of object 'Validation' failed" error. Here are the possible causes and how to resolve them:
Protected worksheet: The sheet on which the data validation is being added can’t be protected, even if the cells to which the validation is being added are not Locked and even if the protection mode is UserInterfaceOnly. You must fully unprotect the sheet, add the validation, and then re-protect the sheet.
Loss of focus by the worksheet cell range: If focus has been taken by any control on the active worksheet (usually a command button) that has been previously clicked by the user, this error will be triggered when the Validation.Add method is subsequently called. (Really, it’s true!) This applies, especially, to any command button that executes the code that adds the validation but it also applies to any control on the worksheet that could be clicked prior to execution of that code. Since there seems to be no legitimate connection between the focus state and the addition of data validation to a cell or range, I consider this an Excel bug. Here are the workarounds:
A. Prevent loss of focus by the worksheet’s cell range: Set the TakeFocusOnClick property of all controls on the worksheet to False.
B. Retrieve focus to the worksheet’s cell range: In the VBA code, prior to executing the Validation.Add method, call the Select method of any cell on the worksheet. The logical choice is to select the cell or range to which data validation is being added, but any cell will do.
第一件事是摆脱 Selection 对象。 它最适合宏记录器:)
顺便说一句，每次您一次又一次地选择相同的块时，即使您正在对其进行更多处理，也请考虑选择在每次迭代中正在处理的块或删除整个块环形。
你可以在 for 循环之后尝试这个吗？
First thing is to get rid of Selection object. It is best suited for Macro Recorder :)
btw in your loop each time you are selecting the same block again and again, even if you are doing some more processing on it, consider selecting the block being worked on in each iteration or remove the whole loop.
Can you try this after you for loop?
我遇到了同样的问题，发现错误与 Application.ReferenceStyle 的设置有关，
请参阅下面更正的代码-
I had the same problem and found the error was related to the setting of Application.ReferenceStyle
See corrected code below-