problem with calender control

[Tim]

I'm having a problem with the calender control. It works
fine until I protect the sheet, then I get a run time error
'1004'.
When I hit debug, ActiveCell.NumberFormat = "dd-mmm-yy" is
hilighted in yellow.

Here is the code.

Private Sub Calendar1_DblClick()
ActiveCell.NumberFormat = "dd-mmm-yy"
ActiveCell = Calendar1.Value
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Range("J9,J10,J11,BP4"),
Target) Is Nothing Then
Calendar1.Left = Target.Left + Target.Width -
Calendar1.Width
Calendar1.Top = Target.Top + Target.Height
Calendar1.Visible = True
Else: Calendar1.Visible = False
End If
End Sub

I'm using Excel 2003.

 

[Paul D]

my first guess would be the active cell you are trying to format is locked.
The cell has to be unlocked to modify it

Paul D

 

[Tim]

I thought that too, but I tried it locked and unlocked and
got the same results.

 

[Ron de Bruin]

Hi Tim

It don't like the separate format line on a protected sheet

Use this

Private Sub Calendar1_DblClick()
ActiveCell = Format(Calendar1.Value, "dd-mmm-yy")
End Sub