Validation not working: run-time error '-2147417848 (80010108)'

[Lara1]

I'm trying to get certain cells to show a hovering alert message when I click on them. (I don't want an error-message style box to pop up, because I'll eventually want it to show for lots of cells at once.)

I recorded a macro to get the code, which I tweaked minimally, and pasted into a clean spreadsheet to test it. It seems to work beautifully. However, when I try to apply the same code within a larger programme, in a working spreadsheet, I get the following error-message:

Run-time error '-2147417848 (80010108)':

Automation error

The object invoked has disonnected from its clients.

(If I click "debug", then Line 15 in Code Sample 2 below is highlighted)



Code Sample 1 - the version that works

Sub ValidationTesting()
 
Dim Row As Long
Row = 10
 
            With Worksheets(1).Cells(Row, 6).Validation
                    .Delete
                    .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator:=xlBetween
                    .IgnoreBlank = True
                    .InCellDropdown = True
                    .InputTitle = "Low Calcium Concentration!"
                    .ErrorTitle = ""
                    .InputMessage = "Ca concentration was too low for the calculation.  It has been adjusted to the minimum value allowed."
                    .ErrorMessage = ""
                    .ShowInput = True
                    .ShowError = True
            End With
 
End Sub
 

Code Sample 2 - the version that doesn't work

Private Sub CaAdjustmentButton_Click()
 
Dim TotalRows As Long
TotalRows = Worksheets("Calculation").UsedRange.Rows.Count
Dim Row As Long
 
For Row = 10 To TotalRows Step 1
 
        If Worksheets("Calculation").Cells(Row, 5) < 5 Then
                Worksheets("Calculation").Cells(Row, 6).Interior.ColorIndex = 3 'red
                Worksheets("Calculation").Cells(Row, 6).Font.ColorIndex = 2 'white
                Worksheets("Calculation").Cells(Row, 6).Value = "5"
                With Worksheets("Calculation").Cells(Row, 6).Validation
                    .Delete
                    .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator:=xlBetween
                    .IgnoreBlank = True
                    .InCellDropdown = True
                    .InputTitle = "Low Calcium Concentration!"
                    .ErrorTitle = ""
                    .InputMessage = "Ca concentration was too low for the calculation.  It has been adjusted to the minimum value allowed."
                    .ErrorMessage = ""
                    .ShowInput = True
                    .ShowError = True
                End With
 
            ElseIf Worksheets("Calculation").Cells(Row, 5).Value > 150 Then
                'Call CaAboveMaximum(Row)
                Worksheets("Calculation").Cells(Row, 6).Interior.ColorIndex = 5 'blue
                Worksheets("Calculation").Cells(Row, 6).Font.ColorIndex = 2 'white
                Worksheets("Calculation").Cells(Row, 6).Value = "150"
            Else:
                'CaWithinRange (Row)
                Worksheets("Calculation").Cells(Row, 6).Interior.ColorIndex = 35 'pale green
                Worksheets("Calculation").Cells(Row, 6).Font.ColorIndex = 1 'black
                Worksheets("Calculation").Cells(Row, 6).Value = Worksheets("Calculation").Cells(Row, 5).Value
        End If
 
Next Row
 
Call InactivateButton
 
MsgBox ("Red or Blue cells indiate input values for Calcium outside allowable range.  Select individual cells for details.")
 
End Sub
 
 

[Lara1]

Okay, following a bit more investigation - the problem ONLY happens when i try to run code sample 2 (see previous post, above) from a button. If I run it from a SheetChange event, or just by itself, it works perfectly.

[Killer42]

Try http://support.microsoft.com/kb/319832

[Lara1]

I already saw that article, before I posted. Trouble was, I couldn't understand any of it :-(

A colleague of a friend has said he thinks the problem is to do with the use of validations, which are complicated (and use external code?). So now i'm trying to figure out how to use shapes with text in, instead of input messages.

[kadghar]

I already saw that article, before I posted. Trouble was, I couldn't understand any of it :-(

A colleague of a friend has said he thinks the problem is to do with the use of validations, which are complicated (and use external code?). So now i'm trying to figure out how to use shapes with text in, instead of input messages.

First of all, are you running your code from VBA in Excel, or from any other VB.
This error happens when you have a variable not defined, so it could be that you're outside Excel's VBA and it doesn't recognize the XL constants.

[Lara1]

First of all, are you running your code from VBA in Excel, or from any other VB.
This error happens when you have a variable not defined, so it could be that you're outside Excel's VBA and it doesn't recognize the XL constants.

hi kadghar - i'm running the code within excel.

to save you putting a lot of time into fixing this, though - i've decided to use AddComment instead, it's working fine and seems to do the job. However, if you have any insight into the problem and want to share it, then please feel free - it's all good learning, for me.

[warloofer]

Hello,

I had the same problem (Run-time error '-2147417848 (80010108)') when I was working in VBA Excel with validation checks.

I solved this selecting the cell where the validation is (range.select) before applying the "validation.add". With this correction, the macro works fine.

In your example, I suppose you will need that:


.....
.....
Worksheets("Calculation").Cells(Row, 6).Value = "5"
Worksheets("Calculation").Cells(Row, 6).Select
With Worksheets("Calculation").Cells(Row, 6).Validation
.....
.....


Regards