Run-Time Error '-2147417848 (80010108)

**SOS** · 05-21-2008, 06:33 PM

Hi folks,

I'm using a piece of code to Data-Validate a couple of columns and it keeps falling over with the error message:
Run-Time Error '-2147417848 (80010108)': Automation error. The object invoked has disconnected from its clients.

The code stops at the red line below:

Private Sub Data_Validate()
Dim FirstWS As Integer, LastWS As Integer, M As Integer

FirstWS = 6
LastWS = 12
For M = FirstWS To LastWS
    With Sheets(M)
        With Columns("C:C").Validation
            .Delete
            .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
            Operator:=xlLessEqual, Formula1:="41"            
            .IgnoreBlank = True
            .InCellDropdown = True
            .ErrorTitle = "Leave Programme"
            .ErrorMessage = "You must enter a numeric value"
            .ShowInput = False
            .ShowError = True
        End With
        With Columns("B:B").Validation
            .Delete
            .Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="4/1/2008", Formula2:="3/31/2009"
            .IgnoreBlank = True
            .InCellDropdown = True
            .ErrorTitle = "Leave Programme"
            .ErrorMessage = _
            "Ensure that the date entered is between 01/04/2008 and 31/03/2009"
            .ShowInput = False
            .ShowError = True
        End With
    End With
Next M

End Sub

Can anyone enlighten me why this happens, please?

Seamus

**shg** · 05-21-2008, 06:50 PM

I think you need to specify both Formula1 and Formula2, even though Formula2 is unused.

You may have discovered Excel's only quirk

**lecxe** · 05-21-2008, 11:18 PM

Hi

Shouldn't Columns be qualified?

Try adding the dot before Columns

        With .Columns("C:C").Validation

HTH
lecxe

**SOS** · 05-22-2008, 07:02 AM

Strange,

I've tried again this morning and am getting a different error message:

"Run-Time Error 1004: Application-defined or Object-defined error"

Even having tried both your suggestions (shg and lecxe) I'm still having problems.

If anyone has any other ideas on how to either deal with the issue or suggest another workaround I'd be grateful.

Col B should only allow dates to be added (1 Apr - 31 Mar) and Col C should only allow numeric data. I thought that Data-Validation was the way to go and decided to add the code below to a button which would add the Data-Validation for me.

But if there is other code out there along the lines of:

DUMMY CODE:

"For each cell in range" or "worksheet change"
Cell.value must be numeric
Next Cell

or something similar I'd like to hear about it.

Many thanks

Seamus

**SOS** · 05-22-2008, 07:43 AM

I changed my mind about using Data-Validation and chode to go down the Worksheet_Change route instead and now have the following code:

Column A has name and code changes it to UPPERCASE on leaving the cell. Col B has date and if not entered in the correct format, shows error message and returns to the offending cell. Col C should be numeric and if not - shows error message and returns to the offending cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then
        Exit Sub
    End If
    On Error GoTo ErrHandler:
'------------------------------------------------
'Change all text to UPPERCASE on leaving the cell
'------------------------------------------------
    If Not Application.Intersect(Me.Range("A:A"), Target) Is Nothing Then
        If IsNumeric(Target.Value) = False Then
            Application.EnableEvents = False
                Target.Value = StrConv(Target.Text, vbUpperCase)
                Application.EnableEvents = True
        End If
    End If
    
'--------------------------------------------------------------------------
'Check if date has been entered correctly formatted and if not show message
'--------------------------------------------------------------------------
    
    If Not Application.Intersect(Me.Range("B:B"), Target) Is Nothing Then
        If IsDate(Target.Value) = False Then
            Application.EnableEvents = False
                MsgBox "Please enter date in the correct format - dd/mm/yyyy"
                With Target
                    .Select
                    .ClearContents
                End With
            Application.EnableEvents = True
        End If
    End If
    
'----------------------------------------------------------
'Check if value is numeric and if not go back and try again
'----------------------------------------------------------
    
    If Not Application.Intersect(Me.Range("C:C"), Target) Is Nothing Then
        If IsNumeric(Target.Value) = False Then
            Application.EnableEvents = False
                MsgBox "You did not enter a numeric value - try again!"
                With Target
                    .Select
                    .ClearContents
                End With
            Application.EnableEvents = True
        End If
    End If
ErrHandler:
    Application.EnableEvents = True
End Sub

**shg** · 05-22-2008, 02:11 PM

Your code will replace formulas with their uppercase results ...

**SOS** · 05-22-2008, 04:47 PM

shg,

But none of the cells are having formulae entered - only Surname, Date and No of Days Leave taken.

Thanks anyway

Seamus

**SOS** · 05-23-2008, 03:49 AM

shg,

I think I see what you meant :
the line If IsNumeric(Target.Value)

I've now changed that part of the sub to:

'------------------------------------------------
'Change all text to UPPERCASE on leaving the cell
'------------------------------------------------
        If Not Application.Intersect(Me.Range("A:A"), Target) Is Nothing Then
            If Application.WorksheetFunction.IsText(Target.Value) Then
                Application.EnableEvents = False
                    Target.Value = StrConv(Target.Text, vbUpperCase)
                    Application.EnableEvents = True
            End If
        End If

Is that better?

Thanks

Seamus