RunTimeerror 2147417848 (80010108) Object invoked disconnected from its clients

[Rem0ram]

Hi
I'm stuck with this run time error.
"Run-Time Error -2147417848 (80010108)
Automation Error
The object invoked has disconnected from its clients."

Below is the code is used in a seperate module and called the same using a Option Button taken from Form Tool.

[vba]Sub BusDiv_CFG()
'Sheets("Input_Sheet").Unprotect "O1"
Rows("23:150").EntireRow.Hidden = False
Range("D19,D42,D52,D63,D74,D84,D94,D104,D114,D124,D134").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=BusDiv_CFG"
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
If Range("D9").Value = "" Or Range("D9").Value = "ORBIT User Access Removal (stop user access to ORBIT)" Then

Rows("21:22").EntireRow.Hidden = False
Rows("24:150").EntireRow.Hidden = True
ElseIf Range("D9").Value = "CLONE Existing ORBIT User" Then

Rows("21:22").EntireRow.Hidden = True
Rows("24:35").EntireRow.Hidden = False
Rows("34:150").EntireRow.Hidden = True

ElseIf Range("D9").Value = "New ORBIT User Request (user does not have access to ORBIT)" Or Range("D9").Value = "ORBIT User Update Request (existing ORBIT user requiring modification)" Or Range("D9").Value = "Other (any other misc user request)" Then

Rows("21:22").EntireRow.Hidden = True
Rows("24:35").EntireRow.Hidden = True
Rows("34:150").EntireRow.Hidden = False
ActiveSheet.Outline.ShowLevels RowLevels:=1
End If
'Sheets("Input_Sheet").Protect "O1"
End Sub[/vba]


When executed in break mode i'm getting the error in the below line:

[vba].Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=BusDiv_CFG"[/vba]

[Rem0ram]

Note:

The "=BusDiv_CFG" refers an Offset Named range.

[Rem0ram]

Hi Excelions

Any help on the above is much appreciated....

Completely....Lost....

[mohanvijay]

Please post workbook

[Rem0ram]

Hii Mohan

Pls find attached the sample file.

thks

[mohanvijay]

In attached workbook there is no range named "BusDiv_CFG"

the "Input_Sheet" has protected so i could not work with that

[mohanvijay]

Sorry i thought "O1" as zero and 1 now i correct it

Try like below this works for me

[vba]

Range("D19,D42,D52,D63,D74,D84,D94,D104,D114,D124,D134").Select
With Selection.Validation
'change above code like this

With Range("D19,D42,D52,D63,D74,D84,D94,D104,D114,D124,D134").Validation

[/vba]

[Rem0ram]

Many Thanks!

But now i'm getting "Method 'Add' of Object validation failed"

Is this something the module contains more than 64k of data?

[Rem0ram]

The above error occors in the BusDiv_CFG() only

[mohanvijay]

i tried in excel 2007 it works fine

i think 64k is not a problem

Check the code again.

did you uncomment the following line?

'Sheets("Input_Sheet").Unprotect "O1"

[Rem0ram]

Apologies!

Fixed it...now it works....

Thanks a Trillion.....! you rocked.......

[mohanvijay]

instead of using lot of procedures you can done by this single procedure by passing range name parameter like below

[vba]
'create main procedure like below

Sub My_List(Rng_Name As String)
Sheets("Input_Sheet").Unprotect "O1"
Rows("21:150").EntireRow.Hidden = False

With Range("D19,D42,D52,D63,D74,D84,D94,D104,D114,D124,D134").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Rng_Name

End Sub

'Create single line procedure for call above procedure

Sub BusDiv_BS()

My_List "=BusDiv_BS"

End sub

Sub BusDiv_CFG()

My_List "=BusDiv_CFG"

End sub
[/vba]

[Rem0ram]

Thanks!

Yes, you are right...i'm still pretty much young to vba....i will now start the code optimization....

Once Again Thanks a lot....

[snb]

You better modify the existing validation instead of deleting validation first an add new ones.

[vba]
Range("D19,D42,D52,D63,D74,D84,D94,D104,D114,D124,D134").Validation.modify xlValidateList, xlValidAlertStop, xlBetween, Rng_Name
[/vba]

[Rem0ram]

Thks Snb....will try....

[Rem0ram]

HI

while using the above code...its working fine but when ever i hit a radio button the hidding & unhidding of rows is visible while triggering a macro...

Is there a way to fix this?

I tried using screen update =false and true at begining and end but still no use.....