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

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

Answer 2

I seem to have moved past the Application-defined or object-defined error by using ActiveWorkbook.ActiveSheet.Range("AO" & firstRow & ":AO" & lastRow) .

However , the following errot still persists Runtime error -2147417848 (80010108) Automation error.The object invoked has disconnected from its clients.

Putting a few msg boxes it seems it is the .Add statement in the with block that conks. I see MsgBoxes upto 4 and then the above error
Am I doing something wrong here? As i said , debugging desn't help. In debug mode , the execution proceeds smoothly and the lsit is duly added

Me.Unprotect Me.Activate

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

MsgBox (1) ActiveWorkbook.ActiveSheet.Range("AO11").Select MsgBox (2) With Selection.Validation MsgBox (3) .Delete MsgBox (4) .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=VOptions MsgBox (5) .IgnoreBlank = True MsgBox (6) .InCellDropdown = True MsgBox (7) .InputTitle = "Options" .ErrorTitle = "" .InputMessage = "Click yes or no" .errorMessage = "" .ShowInput = True .ShowError = True

End With

Me.ProtectSheet

Answer 3

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?

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

Answer 4

OK... I eventually got this working but more through a hack than a solution. It looked like for some reason , by the time the vba code got around to executing some method on the worksheet, the sheet was deactivated. Why this would happen is beyond me? What worked for me is putting the Me.Unprotect right before the Validation .Delete call (as in before the GetResultRowStart and Select calls).

Strangely again, keeping the Me.Activate call where I put the Me.Unprotect didn't work. Hence my inability to reach to a conclusion.

Answer 5

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 6

I find this solution a bit weird. 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. Anyway, thanks for this weird solution. :)