使用VBA在Excel工作表中添加验证列表时出现问题 [英] Problem adding validation list in excel sheet using VBA

问题描述

我有一个Excel工作表，其中装有动态结果数据集.加载所有数据后，我需要在每行的末尾添加YES/NO下拉列表.我必须动态地执行此操作，因为我事先不知道结果集的大小. 以下代码引发应用程序定义或对象定义的错误":

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

GetResultRowStart方法为我提供了开始的行，结果数据已填充到工作表中.我在代码的其他部分也使用了此方法，因此效果很好. 使用消息框进行调试建议在Range(..).select语句上抛出错误.

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.

推荐答案

对此的最终想法:

将工作簿中每个按钮的SetFocusOnClick属性设置为false似乎可以解决问题(到目前为止). 但是，如果这是必需条件，那么将值设置为true根本不起作用.但是，有时确实如此. 但这是我找到的可靠解决方案.

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.

这篇关于使用VBA在Excel工作表中添加验证列表时出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！