Reposting -- no response yet -- issue with removing validation in

[robs3131]

Hi all - I haven't heard anything on this. Just wondering if anyone has any
ideas. Thanks!

--
Robert

Hi,

I'm getting the error below in the code below -- I'm trying to remove
Validation from cells K2:K65536 (all of which have Validation currently). I
don't understand why this error is coming up as I have essentially identical
code in another sheet and get no error with that sheet. I noticed that if I
stop the code right before the remove validation line and actually select the
sheet where the validation removal is to occur and then continue the code,
there is no issue. This doesn't make sense to me as I believe the sheet does
not need to be selected in order for the code to remove the validation.
Thanks for your help!

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

Automation error
The object invoked has disconnected from its clients.

'Code (the line where the error occurs is preceeded by **):
With Sheets("Linkshare Check Input History")
.AutoFilterMode = False
'remove validation
With .Range("K2:K65536").Validation
.Delete
** .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop,
Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
'Insert two columns so that the formula below can be put into
column H
.Columns("H:I").Insert Shift:=xlToRight
'Concatenate values in Check Input History
If Len(.Range("A2")) <> 0 Then
With .Range("H2", .Range("A65536").End(xlUp).Offset(0, 7))
.Formula = _
"=CONCATENATE(RC[-7],RC[-6],RC[-4],RC[-3],RC[-2])"
.Formula = .Value
End With
Else
End If
End With

 

[Conan Kelly]

robs3131,

I'm no expert, but if the only way to get this to work is to select the
sheet before deleting/adding validation, then why not programatically select
the sheet before you add/delete. I don't know if the sheet has to be the
active sheet in order to add/delete validation. Why don't you test? When
this code runs successfully in the other workbook, is the sheet active or is
another sheet active?

Sorry I couldn't be of any more help,

Conan

Hi all - I haven't heard anything on this. Just wondering if anyone has
any
ideas. Thanks!

--
Robert

Hi,

I'm getting the error below in the code below -- I'm trying to remove
Validation from cells K2:K65536 (all of which have Validation currently).
I
don't understand why this error is coming up as I have essentially
identical
code in another sheet and get no error with that sheet. I noticed that
if I
stop the code right before the remove validation line and actually select
the
sheet where the validation removal is to occur and then continue the
code,
there is no issue. This doesn't make sense to me as I believe the sheet
does
not need to be selected in order for the code to remove the validation.
Thanks for your help!

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

Automation error
The object invoked has disconnected from its clients.

'Code (the line where the error occurs is preceeded by **):
With Sheets("Linkshare Check Input History")
.AutoFilterMode = False
'remove validation
With .Range("K2:K65536").Validation
.Delete
** .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop,
Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
'Insert two columns so that the formula below can be put into
column H
.Columns("H:I").Insert Shift:=xlToRight
'Concatenate values in Check Input History
If Len(.Range("A2")) <> 0 Then
With .Range("H2", .Range("A65536").End(xlUp).Offset(0, 7))
.Formula = _
"=CONCATENATE(RC[-7],RC[-6],RC[-4],RC[-3],RC[-2])"
.Formula = .Value
End With
Else
End If
End With

 

[robs3131]

Hi Conan,

Actually, the problem is now occuring even when the sheet is selected...it
is very strange...it doesn't happen the first time I run the macro (it is
fine the first time -- it works perfectly) -- it is the second time I run the
macro over the same range (note that the second time it runs, the validation
set the first time has been removed so there is no validation in the range
already existing at the time it runs a second time).

I have no idea what the issue is....I think I'm just going to put validation
in all cells in the column and never delete cells in that column....
--
Robert

robs3131,

I'm no expert, but if the only way to get this to work is to select the
sheet before deleting/adding validation, then why not programatically select
the sheet before you add/delete. I don't know if the sheet has to be the
active sheet in order to add/delete validation. Why don't you test? When
this code runs successfully in the other workbook, is the sheet active or is
another sheet active?

Sorry I couldn't be of any more help,

Conan

Hi all - I haven't heard anything on this. Just wondering if anyone has
any
ideas. Thanks!

--
Robert

Hi,

I'm getting the error below in the code below -- I'm trying to remove
Validation from cells K2:K65536 (all of which have Validation currently).
I
don't understand why this error is coming up as I have essentially
identical
code in another sheet and get no error with that sheet. I noticed that
if I
stop the code right before the remove validation line and actually select
the
sheet where the validation removal is to occur and then continue the
code,
there is no issue. This doesn't make sense to me as I believe the sheet
does
not need to be selected in order for the code to remove the validation.
Thanks for your help!

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

Automation error
The object invoked has disconnected from its clients.

'Code (the line where the error occurs is preceeded by **):
With Sheets("Linkshare Check Input History")
.AutoFilterMode = False
'remove validation
With .Range("K2:K65536").Validation
.Delete
** .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop,
Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
'Insert two columns so that the formula below can be put into
column H
.Columns("H:I").Insert Shift:=xlToRight
'Concatenate values in Check Input History
If Len(.Range("A2")) <> 0 Then
With .Range("H2", .Range("A65536").End(xlUp).Offset(0, 7))
.Formula = _
"=CONCATENATE(RC[-7],RC[-6],RC[-4],RC[-3],RC[-2])"
.Formula = .Value
End With
Else
End If
End With