V
Vaibhav
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 !
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 !