asked on

EXCEL 2007 MACRO

Hello,
I use this for the excel pop up calendar,

Private Sub Calendar1_Click()
ActiveCell.Value = CDbl(Calendar1.Value)
ActiveCell.NumberFormat = " DDDD 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:D3,N3:N3,X3:X3,AH3:AH3,AR3:AR3,BB3:BB3,BL3:BL3,BV3:BV3,CF3:CF3,CP3:CP3,CZ3:CZ3,DJ3:DJ3"), 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
If Not IsDate(Target.Value) = True Then
Calendar1.Value = Date
Else
Calendar1.Value = Target.Value
End If
ElseIf Calendar1.Visible Then Calendar1.Visible = False
End If
End Sub

If I protect some of my formula cells on the sheet,
and try to run the calendar, I ge terror message Run time error:1004

Any help is appreciated.
Thanks,

Swapnil Nirmal

You need to unprotect sheet to edit cell. so what we will do is unprotect sheet edit required cell an again protect it with VBA. use it like following

Sheets("MySheet").Unprotect Password:="mypassword"
'''Your code here
Sheets("MySheet").Protect Password:="mypassword"

W.E.B

ASKER

Hello,
I get compile error,
Invalid outside procedure.
Thanks,

Swapnil Nirmal

Have you changed the sheet name and password?

W.E.B

ASKER

Correct,
Changed --- My sheets ---- to --- hblaik
Changed --- Password --- to --- 12345

W.E.B

ASKER

compile error,
Invalid outside procedure.

Sheets("hblaik").Unprotect Password:="12345"
Private Sub Calendar1_Click()
ActiveCell.Value = CDbl(Calendar1.Value)
ActiveCell.NumberFormat = " DDDD 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:D3,N3:N3,X3:X3,AH3:AH3,AR3:AR3,BB3:BB3,BL3:BL3,BV3:BV3,CF3:CF3,CP3:CP3,CZ3:CZ3,DJ3:DJ3"), 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
If Not IsDate(Target.Value) = True Then
Calendar1.Value = Date
Else
Calendar1.Value = Target.Value
End If
ElseIf Calendar1.Visible Then Calendar1.Visible = False
End If
End Sub
Sheets("hblaik").Protect Password:="12345"

Swapnil Nirmal

Use it like this:

Private Sub Calendar1_Click()
On Error Resume Next
Sheets("hblaik").Unprotect Password:="12345"
On Error GoTo 0
    ActiveCell.Value = CDbl(Calendar1.Value)
    ActiveCell.NumberFormat = " DDDD mm/dd/yyyy "
    ActiveCell.Select
On Error Resume Next
Sheets("hblaik").Protect Password:="12345"
On Error GoTo 0
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Sheets("hblaik").Protect Password:="12345"
On Error GoTo 0

    If Target.Cells.Count > 1 Then Exit Sub
    If Not Application.Intersect(Range("D3:D3,N3:N3,X3:X3,AH3:AH3,AR3:AR3,BB3:BB3,BL3:BL3,BV3:BV3,CF3:CF3,CP3:CP3,CZ3:CZ3,DJ3:DJ3"), 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
        If Not IsDate(Target.Value) = True Then
        Calendar1.Value = Date
        Else
        Calendar1.Value = Target.Value
        End If
    ElseIf Calendar1.Visible Then Calendar1.Visible = False
    End If

On Error Resume Next
Sheets("hblaik").Protect Password:="12345"
On Error GoTo 0
End Sub

Open in new window

W.E.B

ASKER

Hello,
Appreciate your help,
I get error message,
Runtime Error 1004
Unable to set the NumberFormat property of the Range class
ActiveCell.NumberFormat = " DDDD mm/dd/yyyy "

Thanks,

ASKER CERTIFIED SOLUTION

Swapnil Nirmal

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

W.E.B

ASKER

thanks , works.