Automation Error: The Object Invoked Has Disconnected from Its Clients (Excel)
 

Trying to put in validations for a set of Columns and am
using some macros and functions internally to do so.

Following is the code that I'm using to generate the
validations and then apply them to the specifid columns:

-----------------------------------------------------------
Sub CreateValidation(sheetValidation As String, _
rangeStart As String, rangeEnd As String, _
sheetData As String, colData As Integer, _
rowDataStart As Integer)

On Error GoTo Errorhandler
Sheets(sheetValidation).Range(rangeStart, rangeEnd).Select

With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:=CreateValidationText(sheetData, colData,
rowDataStart)
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

Exit Sub

Errorhandler:
MsgBox Err.Number
MsgBox Err.Description
MsgBox Err.Source

End Sub


Function CreateValidationText(sheetData As String, colData As Integer,
rowDataStart As Integer)

Dim retValue As String

For rowNumber = rowDataStart To 65535
If Sheets(sheetData).Cells(rowNumber, colData).Value = "" Then
Exit For
Else
retValue = retValue & ", " &
Sheets(sheetData).Cells(rowNumber, colData).Value
End If
Next rowNumber

MsgBox "Length: " & Len(retValue)
CreateValidationText = retValue
End Function
-----------------------------------------------------------

I apply the validation using the following call:
CreateValidation "Reports", "H4", "H65535", "DD - Report", 14, 2

I'm using the same code for 7 different sets of columns,
it just fails for the above call and generates the error:
Automation Error: The Object Invoked Has Disconnected from
Its Clients

Please help !