Adding validation fails with automation error

[PhilipAppsWork]

I'm using Visual Basic 6.5 and Excel 2003 SP3. Here's some excerpts
from a sub:

Dim rMyRange As Range

With wksMyWorksheet ' name of a worksheet
Set rMyRange = Range(.Cells(4, 2), .Cells(10, 2))
End With

With rMyRange.Validation
.Delete
.Add Type:=Excel.xlValidateDecimal,
AlertStyle:=Excel.xlValidAlertStop, Operator:=Excel.xlGreaterEqual,
Formula1:="0"
.IgnoreBlank = False
.InCellDropdown = False
.ErrorTitle = "Error"
.ErrorMessage = "Must be >= 0"
.ShowError = True
End With

When I run this, I sometimes get, when adding the validation: Run-time
error '-2147417848 (80010108' : Method 'Add' of object 'Validation'
failed. Sometimes it says "Automation error. The object invoked has
disconnected from its clients".

The workaround I have is to do:
rMyRange.Select
With Selection.Validation
...
End With

instead, which cures the problem, but I don't see why this should be
necessary or why the original code fails. The Microsoft page
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q319832 is not
enlightening.

Any ideas?

Thanks in advance,
Philip.

 

[Jim Cone]

You missed a dot in front of Range...
Set rMyRange = .Range(.Cells(4, 2), .Cells(10, 2))
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



<[email protected]>
wrote in message
I'm using Visual Basic 6.5 and Excel 2003 SP3. Here's some excerpts
from a sub:

Dim rMyRange As Range

With wksMyWorksheet ' name of a worksheet
Set rMyRange = Range(.Cells(4, 2), .Cells(10, 2))
End With
-snip-
When I run this, I sometimes get, when adding the validation: Run-time
error '-2147417848 (80010108' : Method 'Add' of object 'Validation'
failed. Sometimes it says "Automation error. The object invoked has
disconnected from its clients".

The workaround I have is to do:
rMyRange.Select
With Selection.Validation ...
End With
instead, which cures the problem, but I don't see why this should be
necessary or why the original code fails. The Microsoft page
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q319832 is not
enlightening.
Any ideas?
Thanks in advance,
Philip.

 

[Philip]

Thanks for your suggestion - I hadn't thought of doing that. It
doesn't fix it, though - still get the same error with a dot in front
of Range.

Philip.

 

[Jim Cone]

Phillip,
My guess is that you have other unqualified objects in your code.
The KB you referenced is not devoted to clarity but does provide
clues to most of the Excel automation problems posted in this group.

Do you have object references to...
Excel
The Workbook
The Sheet
The Validation object

Do you Set all objects to Nothing before exiting your code?
Are they set to Nothing in child to parent order?
After exiting your code, have you checked Task Manager to see if
an instance of Excel remains?
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Philip"
wrote in message
Thanks for your suggestion - I hadn't thought of doing that. It
doesn't fix it, though - still get the same error with a dot in front
of Range.
Philip.

 

[Philip]

I think I have the fix. The problem is that adding validation to a
range fails if there are no cells selected on the worksheet (I
think). Sometimes this code was being called from a command button
click, and the button took the focus away from the underlying sheet,
meaning that adding the validation failed.

I haven't seen any documentation on when you can add validation to a
range, but this poster below ran into the same issue.

http://www.mcse.ms/message734048.html

Philip.