Hi

Thanks to Ron for posting this code - very useful. I've put it into my
sheet and it
works perfect

I wonder if anyone could help modify it slightly for my needs please?

I have the calendar pop-up when a cell in column A (Date Order Received) is
selected.
In column B I need the user to put another date in (Date Order Shipped - and
I have used your calendar again) HOWEVER - this time (in B) I need some
'intelligence' so that the user cannot select a date which is less than the
date in A. B will always be >= A. Is there a way to modify the code so
column B doesn't return an earlier date than A?

Thanks in advance,

AW

Private Sub Calendar1_Click()
ActiveCell.Value = CDbl(Calendar1.Value)
ActiveCell.NumberFormat = "dd/mmm/yyyy"
ActiveCell.Select
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("a3"), 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

AW,

This will allow any date in column B that is equal to later than the date in column A. It also will
not show the calendar control if column B is selected and column A is empty.

Private Sub Calendar1_Click()
If ActiveCell.Column = 2 Then
If Calendar1.Value < ActiveCell.Offset(0, -1).Value Then
MsgBox "Try to pick a date AFTER " & _
Format(ActiveCell.Offset(0, -1).Value - 1, "mmmm dd, yyyy")
Exit Sub
End If
End If
ActiveCell.Value = CDbl(Calendar1.Value)
ActiveCell.NumberFormat = "dd/mmm/yyyy"
ActiveCell.Select
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("A:B"), Target) Is Nothing Then
If Target.Column = 2 Then
If Target.Offset(0, -1).Value = "" Then Exit Sub
End If
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

HTH,
Bernie
MS Excel MVP


"ArcticWolf" <(E-Mail Removed)> wrote in message
news:8902B944-BDFF-4852-84C7-(E-Mail Removed)...
> Hi
>
> Thanks to Ron for posting this code - very useful. I've put it into my
> sheet and it
> works perfect
>
> I wonder if anyone could help modify it slightly for my needs please?
>
> I have the calendar pop-up when a cell in column A (Date Order Received) is
> selected.
> In column B I need the user to put another date in (Date Order Shipped - and
> I have used your calendar again) HOWEVER - this time (in B) I need some
> 'intelligence' so that the user cannot select a date which is less than the
> date in A. B will always be >= A. Is there a way to modify the code so
> column B doesn't return an earlier date than A?
>
> Thanks in advance,
>
> AW
>
> Private Sub Calendar1_Click()
> ActiveCell.Value = CDbl(Calendar1.Value)
> ActiveCell.NumberFormat = "dd/mmm/yyyy"
> ActiveCell.Select
> 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("a3"), 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

"Twinkle twinkle little star,
Bernie Deitrick that is what you are "

I changed the value to zero so that it returns in the error message box
(brilliant idea - thanks for adding this!!) the same as the date received.

Also, thanks for the swift reply - it's much appreciated.

ATB,

AW

"Bernie Deitrick" wrote:

> AW,
>
> This will allow any date in column B that is equal to later than the date in column A. It also will
> not show the calendar control if column B is selected and column A is empty.
>
> Private Sub Calendar1_Click()
> If ActiveCell.Column = 2 Then
> If Calendar1.Value < ActiveCell.Offset(0, -1).Value Then
> MsgBox "Try to pick a date AFTER " & _
> Format(ActiveCell.Offset(0, -1).Value - 1, "mmmm dd, yyyy")
> Exit Sub
> End If
> End If
> ActiveCell.Value = CDbl(Calendar1.Value)
> ActiveCell.NumberFormat = "dd/mmm/yyyy"
> ActiveCell.Select
> 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("A:B"), Target) Is Nothing Then
> If Target.Column = 2 Then
> If Target.Offset(0, -1).Value = "" Then Exit Sub
> End If
> 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
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "ArcticWolf" <(E-Mail Removed)> wrote in message
> news:8902B944-BDFF-4852-84C7-(E-Mail Removed)...
> > Hi
> >
> > Thanks to Ron for posting this code - very useful. I've put it into my
> > sheet and it
> > works perfect
> >
> > I wonder if anyone could help modify it slightly for my needs please?
> >
> > I have the calendar pop-up when a cell in column A (Date Order Received) is
> > selected.
> > In column B I need the user to put another date in (Date Order Shipped - and
> > I have used your calendar again) HOWEVER - this time (in B) I need some
> > 'intelligence' so that the user cannot select a date which is less than the
> > date in A. B will always be >= A. Is there a way to modify the code so
> > column B doesn't return an earlier date than A?
> >
> > Thanks in advance,
> >
> > AW
> >
> > Private Sub Calendar1_Click()
> > ActiveCell.Value = CDbl(Calendar1.Value)
> > ActiveCell.NumberFormat = "dd/mmm/yyyy"
> > ActiveCell.Select
> > 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("a3"), 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 for letting me know that you got this solution to work.

Bernie
MS Excel MVP

> Also, thanks for the swift reply - it's much appreciated.