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下拉列表。我必须动态地执行此操作,因为我事先不知道结果集的大小。以下代码抛出'Applicaton-defined或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.
GetResultRowStart方法为我提供了一个行,该行开始在工作表中填充结果数据。我已经在代码的其他部分的其他地方使用了这种方法,并且它完美地工作。使用消息框进行调试表明在Range(..)。select语句中抛出错误。
Any ideas about the cause of this error.
关于此错误原因的任何想法。
8 个解决方案
#1
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的值。但是,它有时会这样做。但这是我发现的可靠解决方案。
#2
Let me try to channel my inner-Spolsky here:
让我试着在这里引导我的内心 - 斯波尔斯基:
If you're referring to a range not on the ActiveSheet you should fully qualify the reference.
如果您指的是不在ActiveSheet上的范围,则应完全限定参考。
Something like the following should work:
像下面这样的东西应该工作:
ActiveWorkbook.Sheets("mysheet").Range("AO" & firstRow & ":AO" & lastRow).Select
#3
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%。]
#4
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.
我也遇到了同样的问题,“自动化错误”。我所做的是激活我要将验证列表放入的工作表,错误就消失了。
#5
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,按钮永远不会获得焦点,并且嘿presto,我现在设置验证的代码工作正常。
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.
#6
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.
受保护的工作表:无法保护正在添加数据验证的工作表,即使添加了验证的单元格未锁定,即使保护模式为UserInterfaceOnly也是如此。您必须完全取消保护工作表,添加验证,然后重新保护工作表。
-
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:
工作表单元格范围内的焦点丢失:如果用户先前单击过的活动工作表(通常是命令按钮)上的任何控件都已进行了焦点,则在随后调用Validation.Add方法时将触发此错误。 (确实,这是真的!)这尤其适用于执行添加验证的代码的任何命令按钮,但它也适用于在执行该代码之前可以单击的工作表上的任何控件。由于在焦点状态和向单元格或范围添加数据验证之间似乎没有合法的连接,我认为这是一个Excel错误。以下是解决方法:
A. Prevent loss of focus by the worksheet’s cell range: Set the TakeFocusOnClick property of all controls on the worksheet to False.
A.通过工作表的单元格范围防止失去焦点:将工作表上所有控件的TakeFocusOnClick属性设置为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.
B.将焦点检索到工作表的单元格范围:在VBA代码中,在执行Validation.Add方法之前,调用工作表上任何单元格的Select方法。逻辑选择是选择要添加数据验证的单元格或范围,但任何单元格都可以。
#7
First thing is to get rid of Selection object. It is best suited for Macro Recorder :)
首先是摆脱Selection对象。它最适合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.
每次你一次又一次地选择同一个块时,你的循环中的btw,即使你在它上面做了一些处理,考虑选择每次迭代中正在处理的块或删除整个循环。
Can you try this after you for loop?
你可以在循环后尝试这个吗?
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
#8
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
#1
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的值。但是,它有时会这样做。但这是我发现的可靠解决方案。
#2
Let me try to channel my inner-Spolsky here:
让我试着在这里引导我的内心 - 斯波尔斯基:
If you're referring to a range not on the ActiveSheet you should fully qualify the reference.
如果您指的是不在ActiveSheet上的范围,则应完全限定参考。
Something like the following should work:
像下面这样的东西应该工作:
ActiveWorkbook.Sheets("mysheet").Range("AO" & firstRow & ":AO" & lastRow).Select
#3
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%。]
#4
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.
我也遇到了同样的问题,“自动化错误”。我所做的是激活我要将验证列表放入的工作表,错误就消失了。
#5
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,按钮永远不会获得焦点,并且嘿presto,我现在设置验证的代码工作正常。
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.
#6
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.
受保护的工作表:无法保护正在添加数据验证的工作表,即使添加了验证的单元格未锁定,即使保护模式为UserInterfaceOnly也是如此。您必须完全取消保护工作表,添加验证,然后重新保护工作表。
-
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:
工作表单元格范围内的焦点丢失:如果用户先前单击过的活动工作表(通常是命令按钮)上的任何控件都已进行了焦点,则在随后调用Validation.Add方法时将触发此错误。 (确实,这是真的!)这尤其适用于执行添加验证的代码的任何命令按钮,但它也适用于在执行该代码之前可以单击的工作表上的任何控件。由于在焦点状态和向单元格或范围添加数据验证之间似乎没有合法的连接,我认为这是一个Excel错误。以下是解决方法:
A. Prevent loss of focus by the worksheet’s cell range: Set the TakeFocusOnClick property of all controls on the worksheet to False.
A.通过工作表的单元格范围防止失去焦点:将工作表上所有控件的TakeFocusOnClick属性设置为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.
B.将焦点检索到工作表的单元格范围:在VBA代码中,在执行Validation.Add方法之前,调用工作表上任何单元格的Select方法。逻辑选择是选择要添加数据验证的单元格或范围,但任何单元格都可以。
#7
First thing is to get rid of Selection object. It is best suited for Macro Recorder :)
首先是摆脱Selection对象。它最适合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.
每次你一次又一次地选择同一个块时,你的循环中的btw,即使你在它上面做了一些处理,考虑选择每次迭代中正在处理的块或删除整个循环。
Can you try this after you for loop?
你可以在循环后尝试这个吗?
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
#8
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