Setting a cell's validation data from a macro

[jdvorak]

I am writing an application in vba for excel and need to set the validation list for each cell in a specific column. I have the following code to do this:

Public Sub setReviewerCellValidation(reviewers() As String)
    Dim str As String
    Dim cnt As Integer
    For cnt = 0 To UBound(reviewers)
        If cnt = 0 Then
            str = reviewers(0)
        Else: If reviewers(cnt) <> "" Then str = str & ", " & reviewers(cnt)
        End If
    Next
    
    For Each cell In Range(getColumnRange(RegularReviewerCol))
        cell.Validation.Delete
        cell.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:=str
        cell = ""
   Next
    
End Sub

The string 'str' passed to the Formula1 parameter is series of comma separated names (ex: "name1, name2, name3"). However, when the sub executes, I get the following error message:

Automation error
The object invoked has disconnected from its clients

Anybody know what this means and how to resolve it? Thanks

Joe

 

[9ballpimp]

Option Explicit will help some of the time.

You can also check this:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q319832

Troy