NumberFormat?

[alex]

Hello all,

I am running the following code graciously given to me by a member of
google groups:

Private Sub Calendar1_Click()
ActiveCell.Value = CDbl(Calendar1.Value)
ActiveCell.NumberFormat = "mm/dd/yyyy"
ActiveCell.Select
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("D3100"), Target) Is Nothing
Then
Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
Calendar1.Top = Target.Top + Target.Height
Calendar1.Visible = True
' select Today's date in the Calendar
Calendar1.Value = Date
ElseIf Calendar1.Visible Then Calendar1.Visible = False
End If
End Sub

It works fine until I protect the worksheet. Even though a particular
cell is unlocked (such as D4) I'm getting a run-time error '1004'
'Unable to set the NumberFormat property of the Range class'

I have no restrictions set to false under 'Protect Sheet'
I have tried formatting the cells with general, date, number; nothing
works.

Any thoughts?

alex

 

[Alan]

Try this modification

Private Sub Calendar1_Click()
Sheet1. Unprotect ("Password")
ActiveCell.Value = CDbl(Calendar1.Value)
ActiveCell.NumberFormat = "mm/dd/yyyy" ActiveCell.Select
Sheet1. Protect ("Password")
End Sub

Do the same in the other code, ie wrap the code in t 'Unprotect - Protect'
so that the main body runs on an unprotected sheet.Obviously alter the sheet
number and password to suit,
Regards,
Alan.

 

[alex]

Try this modification

Private Sub Calendar1_Click()
Sheet1. Unprotect ("Password")
ActiveCell.Value = CDbl(Calendar1.Value)
ActiveCell.NumberFormat = "mm/dd/yyyy" ActiveCell.Select
Sheet1. Protect ("Password")
End Sub

Do the same in the other code, ie wrap the code in t 'Unprotect - Protect'
so that the main body runs on an unprotected sheet.Obviously alter the sheet
number and password to suit,
Regards,












- Show quoted text -

Alan you're a genius.

Only problem, is that now whenever I select a cell, the worksheet
automatically protects itself.

 

[Alan]

In that case remove the line that protects the sheet automatically and just
protect it manually if you want to,
Regards,
Alan.