How to limit date range in calendar control?

Hi,

I need to limit the date range available/allowed to choose from in a drop down calendar created with calendar control 12 in excel.

The date range is given in two cells in the sheet. Min date in cell I5 and max date in cell J5.

Any suggestion of how to do this with a VBA code?


Right now, my VBA code looks like this:

Private Sub Calendar1_Click()
    ActiveCell.Value = CDbl(Calendar1.Value)
    ActiveCell.NumberFormat = "dd.mm.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("I10:J44"), 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

Thanks!

Please use code tags. Forum Rule #3 describes why and how to do that

3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Select your code and click the [#] button at the top of the post window (if you are editing an existing post, press Go Advanced to see the [#] button).

Something like this maybe

  Private Sub Calendar1_Click()
   With ActiveCell
   .Value = CDbl(Calendar1.Value)
   .NumberFormat = "dd.mm.yyyy"
    If .Value <= Range("I15").Value Then
        MsgBox "Date too early"
    End If
    End With
End Sub

Add a similar check for the other date