#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 48
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 492
Default 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


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 48
Default 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.

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 492
Default 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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Format numberformat Single BenCoo Excel Worksheet Functions 1 March 17th 06 07:17 PM
using cells.numberformat maxzsim Excel Worksheet Functions 2 May 6th 05 10:41 AM
Setting NumberFormat within a VBA Module Dominic Olivastro Excel Discussion (Misc queries) 3 April 20th 05 12:10 AM


All times are GMT +1. The time now is 12:27 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"