Repost- problem with calender control

[Tim]

I posted this last night but it might have be a bit late.

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.

 

[Frank Kabel]

Hi
one idea (not tested): Prior to protecting the sheet you should enable
data entries for the specific cells ('Format - Cells - Protection') as
you're trying to insert values in cells on your protected sheet

Another one would be to unprotect the sheet at the beginning of your
code and to re-protect it after you inserted your values

 

[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

 

[Tim]

Neither did Excel, that fixed the problem.

Thanks Ron

-----Original Message-----
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)




"Tim" <[email protected]> wrote in

message news:[email protected]...

 

[Ron de Bruin]

Neither did Excel
I mean Excel <g>

I changed it on my page also
http://www.rondebruin.nl/calendar.htm