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.

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" wrote in message
...
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.

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




-----Original Message-----
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" wrote in message
...
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.


.

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


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Tim" wrote in message ...
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.