Problem adding validation list in excel sheet using VBA

Question

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.

Answer 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.

Answer 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.

Something like the following should work:

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