Exit Sub On Protected Sheet With Calendar Control

[ChuckRobert]

I have a protected sheet that contains calender controls based on code from Ron de Bruin http://www.rondebruin.nl/calendar.htm (pasted below). The sheet is primarily a reference, but may be edited on occasion. To enable the calender, the sheet must be unprotected, but if an attempt to enter a date in the calendar is made while the sheet is still protected, the user gets a runtime 1004 error, and clicking degug sends the user to the code.
Ideally, I'd like to bring up a MsgBox to notify the user to unprotect the worksheet, then Exit Sub (before the runtime error surfaces). Here is the code I am using -



Private Sub Calendar1_Click()
</PRE>
ActiveCell.Value = CDbl(Calendar1.Value)
ActiveCell.Select
Calendar1.Visible = False

End Sub
</PRE>
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("C13:D1050,I13:J1050,L13"), 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


</PRE>

Any suggestions?
</PRE>

 

[RoryA]

Try something like:

Private Sub Calendar1_Click()
If Activesheet.ProtectContents then
   msgbox "Sheet must be unprotected first!"
Else
ActiveCell.Value = CDbl(Calendar1.Value)
ActiveCell.Select
End If
Calendar1.Visible = False

End Sub

 

[ChuckRobert]

Rory,
It works perfectly - thanks for the great service!!

Chuck

 

[ChuckRobert]

I was wondering if you see a way that I could exempt cell L13 from the special treatment (to enable the calendar control while the worksheet is still protected, without the MsgBox coming up)? I use L13 to select dates for my advanced filter. I played with it for a while today, but didn't have any luck. Here is the current code -

Private Sub Calendar1_Click()
If Activesheet.ProtectContents then
msgbox "Sheet must be unprotected first!"
Else
ActiveCell.Value = CDbl(Calendar1.Value)
ActiveCell.Select
End If
Calendar1.Visible = False

End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("C13:D1050,I13:J1050,L13"), 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

 

[RoryA]

You could use:

Private Sub Calendar1_Click()
If Activesheet.ProtectContents then
   If Activecell.address = "$L$13" Then
      ActiveCell.Value = CDbl(Calendar1.Value)
 ActiveCell.Select
else
   msgbox "Sheet must be unprotected first!"
end if
Else
ActiveCell.Value = CDbl(Calendar1.Value)
ActiveCell.Select
End If
Calendar1.Visible = False

End Sub

 

[ChuckRobert]

Rory,
It works great with this adjustment! Thanks again!!

Chuck