使用 VBA 在 Excel 表中添加验证列表时出现问题

Problem adding validation list in excel sheet using VBA

提问by atlantis

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':

我有一个加载了动态数据结果集的 Excel 工作表。加载所有数据后，我需要在每行末尾添加一个 YES/NO 下拉列表。我必须动态执行此操作，因为我事先不知道结果集的大小。以下代码抛出“应用程序定义或对象定义错误”：

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.

GetResultRowStart 方法为我提供了开始在工作表中填充结果数据的行。我也在代码的其他部分的其他地方使用过这种方法，它工作得很好。使用消息框调试建议在 Range(..).select 语句中抛出错误。

Any ideas about the cause of this error.

有关此错误原因的任何想法。

回答by atlantis

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.

将工作簿中每个按钮的 SetFocusOnClick 属性设置为 false 似乎已经解决了这个问题（至少现在是这样）。但是，如果这是必需的条件，则在将值设置为 true 的情况下它根本不应该起作用。然而，有时确实如此。但这是我找到的可靠解决方案。

回答by mechanical_meat

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.

如果您指的是不在 上的范围，ActiveSheet则应完全限定参考。

Something like the following should work:

像下面这样的东西应该工作：

ActiveWorkbook.Sheets("mysheet").Range("AO" & firstRow & ":AO" & lastRow).Select

回答by pstraton

This is a variation on the "Method 'Add' of object 'Validation' failed" error. Here are the possible causes and how to resolve them:

这是“对象'验证'的方法'添加'失败”错误的变体。以下是可能的原因以及解决方法：

1. 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 Lockedand even if the protection mode is UserInterfaceOnly. You must fully unprotect the sheet, add the validation, and then re-protect the sheet.

2. 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.Addmethod 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 TakeFocusOnClickproperty 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.Addmethod, call the Selectmethod 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.

1. 受保护的工作表：无法保护添加数据验证的工作表，即使添加验证的单元格未锁定，即使保护模式为UserInterfaceOnly 也是如此。您必须完全取消保护工作表，添加验证，然后重新保护工作表。

2. 工作表单元格范围失去焦点：如果用户先前单击的活动工作表（通常是命令按钮）上的任何控件已获得焦点，则在随后调用Validation.Add方法时将触发此错误. （真的，这是真的！）这尤其适用于执行添加验证的代码的任何命令按钮，但它也适用于工作表上可以在执行该代码之前单击的任何控件。由于焦点状态与向单元格或区域添加数据验证之间似乎没有合法的联系，我认为这是一个 Excel 错误。以下是解决方法：

   A.防止工作表的单元格范围失去焦点：将工作表上所有控件的TakeFocusOnClick属性设置为False。

   B.将焦点检索到工作表的单元格区域：在 VBA 代码中，在执行Validation.Add方法之前，调用工作表上任何单元格的Select方法。合乎逻辑的选择是选择要添加数据验证的单元格或范围，但任何单元格都可以。

回答by pstraton

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.

我也遇到了同样的问题，“自动化错误”。我所做的是激活我要放入验证列表的工作表，错误就消失了。

回答by genoki

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%.]

我使用的解决方案是在使用 xx.validation 之前取消保护工作表，然后在之后保护。[我不必在 Excel 2000 中执行此操作，我认为我不必在 Excel 2003 中执行此操作，直到可能添加了服务包，但不能说 100%。]

回答by Chris Perry

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.

我刚刚在 Excel 中遇到了一个非常相似的问题。我发现以编程方式设置验证下拉列表的代码在立即窗口中运行时运行良好，但在从工作表上的按钮调用时不起作用。我现在意识到这是因为按钮具有焦点，并且在设置验证之前尝试选择或激活代码中的工作表或单元格似乎无法解决此问题。但是，我刚刚意识到 Excel 中的按钮有一个“TakefocusOnClick”属性，默认情况下设置为 True。通过将此设置为 False，按钮永远不会获得焦点，嘿，我的代码设置验证现在可以正常工作了。

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.

干杯。

回答by Kevin McClafferty

I had the same problem and found the error was related to the setting of Application.ReferenceStyle

我遇到了同样的问题，发现错误与 Application.ReferenceStyle 的设置有关

See corrected code below-

请参阅下面更正的代码-

If Application.ReferenceStyle = xlR1C1 Then
  .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=R1C16:R" & foldercnt & "C16"
Else
  .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=$P1:$P" & foldercnt
End If

回答by Adarsha

First thing is to get rid of Selection object. It is best suited for Macro Recorder :)

首先是摆脱 Selection 对象。它最适合宏记录器:)

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?

你可以在 for 循环后试试这个吗？

With ActiveWorkbook.ActiveSheet.Range("AO" & firstRow & ":AO" & lastRow).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