View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Conan Kelly Conan Kelly is offline
external usenet poster
 
Posts: 419
Default Reposting -- no response yet -- issue with removing validation in

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





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

--
Robert


"robs3131" wrote:

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
--
Robert



--
Robert