Hi S,

Try protecting the sheet using the rtotect method's
UserInterfaceOnly argument: in this way, the sheet
will remain protected for the user, but VBA will be
able to manipulate the sheet.

However, this setting is not persistent and needs to
be reset each time the workbook is opened.

Perhaps, therefore, you could set protection in the
Workbook_Open or Auto_Open procedures, e.g.:

'=============
Sub Auto_Open()
With Worksheets("sheet1")
.Protect Password:="drowssap", _
UserInterfaceOnly:=True
.EnableAutoFilter = True 'Optional setting
EnableOutlining = True 'Otional setting
End With
End Sub
'<<=============


---
Regards,
Norman


wrote in message
oups.com...
Dear All,
I was able to take all the great info from
microsoft.public.excel.programming and create pop-up calendars in my
Excel spreadsheet. The problem is that they wont function properly
when I protect my worksheet. I keep getting the error shown below.
Any advice?


Run-time error '1004':

Unable to set the NumberFormat property of the Range class