PDA

View Full Version : RunTimeerror 2147417848 (80010108) Object invoked disconnected from its clients



Rem0ram
02-13-2013, 01:59 PM
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.

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


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

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=BusDiv_CFG"

Rem0ram
02-13-2013, 02:12 PM
Note:

The "=BusDiv_CFG" refers an Offset Named range.

Rem0ram
02-13-2013, 02:23 PM
Hi Excelions

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

Completely....Lost....:dunno

mohanvijay
02-14-2013, 12:00 AM
Please post workbook

Rem0ram
02-14-2013, 01:43 AM
Hii Mohan

Pls find attached the sample file.

thks

mohanvijay
02-14-2013, 02:21 AM
In attached workbook there is no range named "BusDiv_CFG"

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

mohanvijay
02-14-2013, 02:46 AM
Sorry i thought "O1" as zero and 1 now i correct it

Try like below this works for me



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

Rem0ram
02-14-2013, 02:55 AM
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
02-14-2013, 02:59 AM
The above error occors in the BusDiv_CFG() only

mohanvijay
02-14-2013, 03:01 AM
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
02-14-2013, 03:03 AM
Apologies!

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

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

mohanvijay
02-14-2013, 03:13 AM
instead of using lot of procedures you can done by this single procedure by passing range name parameter like below


'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

Rem0ram
02-14-2013, 03:31 AM
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
02-14-2013, 03:39 AM
You better modify the existing validation instead of deleting validation first an add new ones.


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

Rem0ram
02-14-2013, 05:25 AM
Thks Snb....will try....

Rem0ram
02-14-2013, 11:42 AM
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.....