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,

Microsoft ExcelVB ScriptMicrosoft Office

Last Comment

W.E.B

8/22/2022 - Mon

Swapnil Nirmal

7/11/2011

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

7/11/2011

ASKER

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

Swapnil Nirmal

7/11/2011

Have you changed the sheet name and password?

Your help has saved me hundreds of hours of internet surfing.

fblack61

W.E.B

7/11/2011

ASKER

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

W.E.B

7/11/2011

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

7/12/2011

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

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

W.E.B

7/12/2011

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

7/12/2011

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

W.E.B

7/12/2011

ASKER

thanks , works.