Can't get the popup calendar to work when I protect the workbook

[Patty]

I have the following code in my workbook to bring up a calendar.
Private Sub Calendar1_Click()
ActiveCell = Calendar1.Value
ActiveCell.NumberFormat = "mm/dd/yy"
End Sub
Private Sub UserForm_Activate()
Me.Calendar1.Value = Date
End Sub

When I choose a date on the calendar, I get the following message:
Run-time error '1004'
Unable to set the NumberFormat property of the range class

If I choose End, the date appears in the cell.

I want to publish this form on our website and don't want this message to
appear. It only happens when I protect the worksheet. The date cell is
unlocked.

How do I fix?

 

[Dave Peterson]

Is your worksheet protected (with that cell unlocked).

The "unlockedness" of the cell means that you can change the value, but you may
not be able to change the formatting (including the .numberformat).

 

[Patty]

Yes. The worksheet is protected with this cell unlocked. How do I prevent
the run-time error?

 

[Dave Peterson]

Add a line to unprotect the sheet, do the work, and then add a line to reprotect
the sheet.

activesheet.unprotect password:="TopSecret"
with activecell
.numberformat = "mm/dd/yy"
.value = calendar1.value
end with
activesheet.protect password:="TopSecret"

 

[Patty]

The password for my workbook is 'X'. I inserted the code you gave me, but
probably not in the right place because it still doesn't work.

Can you arrange properly for me so I can just copy and paste?

Here is what I have now:
Private Sub Calendar1_Click()
ActiveSheet.Unprotect Password:="X"
With ActiveCell
..NumberFormat = "mm/dd/yy"
..Value = Calendar1.Value
End With
ActiveSheet.Protect Password:="X"
ActiveCell = Calendar1.Value
ActiveCell.NumberFormat = "mm/dd/yy"
End Sub
Private Sub UserForm_Activate()
Me.Calendar1.Value = Date
End Sub

 

[Dave Peterson]

Private Sub Calendar1_Click()
ActiveSheet.Unprotect Password:="X"
With ActiveCell
.NumberFormat = "mm/dd/yy"
.Value = Calendar1.Value
End With
ActiveSheet.Protect Password:="X"
End Sub
Private Sub UserForm_Activate()
Me.Calendar1.Value = Date
End Sub

The password for my workbook is 'X'. I inserted the code you gave me, but
probably not in the right place because it still doesn't work.

Can you arrange properly for me so I can just copy and paste?

Here is what I have now:
Private Sub Calendar1_Click()
ActiveSheet.Unprotect Password:="X"
With ActiveCell
.NumberFormat = "mm/dd/yy"
.Value = Calendar1.Value
End With
ActiveSheet.Protect Password:="X"
ActiveCell = Calendar1.Value
ActiveCell.NumberFormat = "mm/dd/yy"
End Sub
Private Sub UserForm_Activate()
Me.Calendar1.Value = Date
End Sub

 

[Patty]

It works beautifully. Thanks a million!