Pop-up calendar with protected sheet

[S.Data]

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

 

[Ron de Bruin]

Have you read this on my site

http://www.rondebruin.nl/calendar.htm

Note: If you protect your sheet in Excel 97-2000 then you must format the range first with
the Date format you want and remove this line ActiveCell.NumberFormat = "mm/dd/yyyy"
In Excel 2002 and 2003 you be able to protect your worksheet and allow Format cells.

 

[Norman Jones]

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
'<<=============

 

[Norman Jones]

Hi S,

EnableOutlining = True 'Otional setting

Should read:

.EnableOutlining = True 'Otional setting