"Automation Error"
 

Hi

I'm trying to create an Excel worksheet that automatically logs start
time, date, initials etc. for work being done in a lab. This sheet needs
to be protected so that some people can edit it and others may only view
it. After creating and testing this sheet I manually applied the
protection I wanted. When I tested it after I applying protection I
receive an "Automation Error". It always stops when trying to create a
drop down list in a cell.
Here is where the problem seems to be...

With Selection.Validation
.Delete
STOPS HERE -- .Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop,Operator:= _
xlBetween, Formula1:="=$A65530:$a65536"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

I generated this by recording the steps it took to create a list using
Data-Validation. I appreciate any help you can provide.

Thanks,
Matt

"Automation Error"
 

Your code runs for me, unless worksheet protection is set. In which case I
too get an automation error. So if you're setting protection do it after
the rest of the code is run.

--
Jim Rech
Excel MVP