have a date cell pop up a monthly calendar to choose a date

L

lillywhite

Can I create a date cell that will pop up a monthly calendar from which I can
choose a date, and which will allow me to scroll forward or back through the
months of the year and even into future years.
 
V

venspen

lillywhite said:
Can I create a date cell that will pop up a monthly calendar from which I can
choose a date, and which will allow me to scroll forward or back through the
months of the year and even into future years.
Click to expand...

Put a monthview control on your worksheet near the cell you want to enter
the date into. Set it's visible property to false.

In this case when the user enters cell A6 the monthview will become visible.
When the user double clicks on the date they want A6 will now have that date
and the monthview will hide itself.

In the sheets code window paste the following code:

Private Sub MonthView1_DblClick()
With MonthView1
ActiveCell.Value = .Value
.Visible = False
End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim rng As Range
Set rng = Application.Intersect(Target, Range("B6"))
With MonthView1
If Not rng Is Nothing Then
.Visible = True
.Top = Target.Top
.Left = Target.Left
Else
.Visible = False
End If
End With
End Sub
 
R

Rob

Hi,

I'd also like to do what lillywhite asked but can't figure out where the
monthview object comes from - can you perhaps give me some pointers.
Thanks, Rob
 
V

venspen

Rob said:
Hi,

I'd also like to do what lillywhite asked but can't figure out where the
monthview object comes from - can you perhaps give me some pointers.
Thanks, Rob
Click to expand...

Sorry about that, my bad! I think that Monthview is a control that is
installed with Visual Basic 6. You can use the Calendar Control in the same
fashion:

Private Sub Calendar1_DblClick()
With Calendar1
ActiveCell.Value = .Value
.Visible = False
End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim rng As Range
Set rng = Application.Intersect(Target, Range("B6"))
With Calendar1
If Not rng Is Nothing Then
.Visible = True
.Top = Target.Top
.Left = Target.Left
Else
.Visible = False
End If
End With
End Sub
 
Top