NumberFormat?
 

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("D3:D100"), 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

NumberFormat?
 

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" wrote in message
ps.com...
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("D3:D100"), 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

NumberFormat?
 

On Mar 1, 3:44 pm, "Alan" wrote:
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" wrote in message

ps.com...



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("D3:D100"), 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- Hide quoted text -

- Show quoted text -
Alan you're a genius.

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

NumberFormat?
 

In that case remove the line that protects the sheet automatically and just
protect it manually if you want to,
Regards,
Alan.
"alex" wrote in message
oups.com...
On Mar 1, 3:44 pm, "Alan" wrote:
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" wrote in message

ps.com...



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("D3:D100"), 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- Hide quoted text -

- Show quoted text -
Alan you're a genius.

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