Pop-up Calendars in an Excel Spreadsheet
 

Hi,

I have checked every resource I can find (including Microsoft online help),
but cannot find a solution to my problem.

I am wanting to assign a pop-up calendar to a column of cells in an Excel
spreadsheet so that when I click on the cell the pop-up calendar (current
month)will appear so that I can select a date from either the existing month
showing or scroll through using arrow buttons on the calendar to future
months. Once I select a date, I want the date to appear in text form within
the cell (much like what Microsoft Money does when entering a transaction
date). I am positive I have seen such feature in a previous Excel
spreadsheet (which I, unfortunately, dont have and dont remember where I
saw it).



Please tell me that this can be done and, if so, how to do it. I am only
generally versed in Excel (up to the point of functions, but not Pivot
Tables, ActiveX or Macros). If you feel this would be beyond my scope, could
you please provide me with step-by-step instructions on how to build a single
column of about 1,000 cells with a pop-up calendar assigned to each cell?

In case it is necessary, I have Microsoft Excel 2003 running on Windows XP
Home Edition, Version 2002, Service Pack 2.

Rick

Pop-up Calendars in an Excel Spreadsheet
 

Put this in the Worksheet Code Module that you want the calendar in:

Private Sub Calendar1_DblClick()
ActiveCell.NumberFormat = "m/d/yyyy"
ActiveCell = Calendar1.Value
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Range("J1:J1000"), Target) Is Nothing Then
'(adjust for your range)
Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
Calendar1.Top = Target.Top + Target.Height
Calendar1.Value = Now()
Calendar1.Visible = True
Else: Calendar1.Visible = False
End If
End Sub

Mike F
"Trickster" wrote in message
...
Hi,

I have checked every resource I can find (including Microsoft online
help),
but cannot find a solution to my problem.

I am wanting to assign a pop-up calendar to a column of cells in an Excel
spreadsheet so that when I click on the cell the pop-up calendar (current
month)will appear so that I can select a date from either the existing
month
showing or scroll through using arrow buttons on the calendar to future
months. Once I select a date, I want the date to appear in text form
within
the cell (much like what Microsoft Money does when entering a transaction
date). I am positive I have seen such feature in a previous Excel
spreadsheet (which I, unfortunately, don't have and don't remember where I
saw it).



Please tell me that this can be done and, if so, how to do it. I am only
generally versed in Excel (up to the point of functions, but not Pivot
Tables, ActiveX or Macros). If you feel this would be beyond my scope,
could
you please provide me with step-by-step instructions on how to build a
single
column of about 1,000 cells with a pop-up calendar assigned to each cell?

In case it is necessary, I have Microsoft Excel 2003 running on Windows XP
Home Edition, Version 2002, Service Pack 2.

Rick

Pop-up Calendars in an Excel Spreadsheet
 

And you will need to add a calendar to your sheet...from the menus
InsertObjectCalendar Control 10.0.

Regards
Rowan

Mike Fogleman wrote:
Put this in the Worksheet Code Module that you want the calendar in:

Private Sub Calendar1_DblClick()
ActiveCell.NumberFormat = "m/d/yyyy"
ActiveCell = Calendar1.Value
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Range("J1:J1000"), Target) Is Nothing Then
'(adjust for your range)
Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
Calendar1.Top = Target.Top + Target.Height
Calendar1.Value = Now()
Calendar1.Visible = True
Else: Calendar1.Visible = False
End If
End Sub

Mike F
"Trickster" wrote in message
...

Hi,

I have checked every resource I can find (including Microsoft online
help),
but cannot find a solution to my problem.

I am wanting to assign a pop-up calendar to a column of cells in an Excel
spreadsheet so that when I click on the cell the pop-up calendar (current
month)will appear so that I can select a date from either the existing
month
showing or scroll through using arrow buttons on the calendar to future
months. Once I select a date, I want the date to appear in text form
within
the cell (much like what Microsoft Money does when entering a transaction
date). I am positive I have seen such feature in a previous Excel
spreadsheet (which I, unfortunately, don't have and don't remember where I
saw it).



Please tell me that this can be done and, if so, how to do it. I am only
generally versed in Excel (up to the point of functions, but not Pivot
Tables, ActiveX or Macros). If you feel this would be beyond my scope,
could
you please provide me with step-by-step instructions on how to build a
single
column of about 1,000 cells with a pop-up calendar assigned to each cell?

In case it is necessary, I have Microsoft Excel 2003 running on Windows XP
Home Edition, Version 2002, Service Pack 2.

Rick

Pop-up Calendars in an Excel Spreadsheet
 

Rowan,

You sound like you might be able to explain this a little easier to me.
Mike had written:

Put this in the Worksheet Code Module that you want the calendar in:

Private Sub Calendar1_DblClick()
ActiveCell.NumberFormat = "m/d/yyyy"
ActiveCell = Calendar1.Value
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Range("J1:J1000"), Target) Is Nothing Then
'(adjust for your range)
Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
Calendar1.Top = Target.Top + Target.Height
Calendar1.Value = Now()
Calendar1.Visible = True
Else: Calendar1.Visible = False
End If
End Sub

And then you added:

And you will need to add a calendar to your sheet...from the menus
InsertObjectCalendar Control 10.0.

I'm not sure where to even start. I know how to do most functions, but when
it comes to macros, etc., I'm lost. Can you possibly give me a step by step
procedure for setting up a column with pop-up calendars in each cell in the
column. Probably best to treat me as if you were writing Excel for Dummies?

I would be greatly in your debt if you could help me out.

Rick

"Rowan Drummond" wrote:

And you will need to add a calendar to your sheet...from the menus
InsertObjectCalendar Control 10.0.

Regards
Rowan

Mike Fogleman wrote:
Put this in the Worksheet Code Module that you want the calendar in:

Private Sub Calendar1_DblClick()
ActiveCell.NumberFormat = "m/d/yyyy"
ActiveCell = Calendar1.Value
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Range("J1:J1000"), Target) Is Nothing Then
'(adjust for your range)
Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
Calendar1.Top = Target.Top + Target.Height
Calendar1.Value = Now()
Calendar1.Visible = True
Else: Calendar1.Visible = False
End If
End Sub

Mike F
"Trickster" wrote in message
...

Hi,

I have checked every resource I can find (including Microsoft online
help),
but cannot find a solution to my problem.

I am wanting to assign a pop-up calendar to a column of cells in an Excel
spreadsheet so that when I click on the cell the pop-up calendar (current
month)will appear so that I can select a date from either the existing
month
showing or scroll through using arrow buttons on the calendar to future
months. Once I select a date, I want the date to appear in text form
within
the cell (much like what Microsoft Money does when entering a transaction
date). I am positive I have seen such feature in a previous Excel
spreadsheet (which I, unfortunately, don't have and don't remember where I
saw it).



Please tell me that this can be done and, if so, how to do it. I am only
generally versed in Excel (up to the point of functions, but not Pivot
Tables, ActiveX or Macros). If you feel this would be beyond my scope,
could
you please provide me with step-by-step instructions on how to build a
single
column of about 1,000 cells with a pop-up calendar assigned to each cell?

In case it is necessary, I have Microsoft Excel 2003 running on Windows XP
Home Edition, Version 2002, Service Pack 2.

Rick

Pop-up Calendars in an Excel Spreadsheet
 

Hi Rick

Mike's code will do what you have requested as long as you have inserted
a calendar onto the sheet. So step by step:

1.) In Excel select the sheet you want to apply the calendar to (any
cell for now).
2.) From the Menu's select InsertObject. This will bring up a dialog
box with a list of available Controls. Select "Calendar Control xx.x"
where xx.x is the version number. In my version of Excel 2002 this is
10.0 but it shouldn't make a difference if you have a newer version with
Excel 2003.
3.) You should now be able to see a calendar on the sheet. You may also
have an small floating toolbar with one button (Design Mode). You can
close this toolbar by clicking on the x.
4.) Right click the sheet tab and select View Code.
5.) Paste Mike's code onto the Code Module that is displayed.
6.) On the 6th line of code adjust the range to be the 1000 cells that
you would like to have the calendar linked to - Mike has assumed J1:J1000.
7.) Press Alt+Q to return to excel
8.) Select any cell OUTSIDE of the range you have specified. The
calendar should dissapear.
9.) Select any cell INSIDE the range you have specified. The calendar
will pop-up. Doubleclick on a date in the calendar and that date will
appear in the cell you selected. The calendar will remain visible until
you click on a cell outside of the range specified.

If I was implementing this for myself I would change the functionality
slightly so that only one click on the calendar is required and once a
date is selected the calendar is hidden - personal preference. To do
this right click the sheet tab again and select view code.

Replace these lines:

Private Sub Calendar1_DblClick()
ActiveCell.NumberFormat = "m/d/yyyy"
ActiveCell = Calendar1.Value
End Sub

With:

Private Sub Calendar1_Click()
ActiveCell.NumberFormat = "m/d/yyyy"
ActiveCell = Calendar1.Value
Calendar1.Visible = False
End Sub

Press Alt+Q again to return to excel.

Hope this helps
Rowan


Trickster wrote:
Rowan,

You sound like you might be able to explain this a little easier to me.
Mike had written:

Put this in the Worksheet Code Module that you want the calendar in:

Private Sub Calendar1_DblClick()
ActiveCell.NumberFormat = "m/d/yyyy"
ActiveCell = Calendar1.Value
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Range("J1:J1000"), Target) Is Nothing Then
'(adjust for your range)
Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
Calendar1.Top = Target.Top + Target.Height
Calendar1.Value = Now()
Calendar1.Visible = True
Else: Calendar1.Visible = False
End If
End Sub

And then you added:

And you will need to add a calendar to your sheet...from the menus
InsertObjectCalendar Control 10.0.

I'm not sure where to even start. I know how to do most functions, but when
it comes to macros, etc., I'm lost. Can you possibly give me a step by step
procedure for setting up a column with pop-up calendars in each cell in the
column. Probably best to treat me as if you were writing Excel for Dummies?

I would be greatly in your debt if you could help me out.

Rick

Pop-up Calendars in an Excel Spreadsheet
 

Hi Trickster,

Mike's code will do what you have requested as long as you have inserted
a calendar onto the sheet.

And if you do not have a Calendar control, see Ron De Bruin's Calendar page:

http://www.rondebruin.nl/calendar.htm

for a download link.

---
Regards,
Norman


"Rowan Drummond" wrote in message
...
Hi Rick

Mike's code will do what you have requested as long as you have inserted a
calendar onto the sheet. So step by step:

1.) In Excel select the sheet you want to apply the calendar to (any cell
for now).
2.) From the Menu's select InsertObject. This will bring up a dialog box
with a list of available Controls. Select "Calendar Control xx.x" where
xx.x is the version number. In my version of Excel 2002 this is 10.0 but
it shouldn't make a difference if you have a newer version with Excel
2003.
3.) You should now be able to see a calendar on the sheet. You may also
have an small floating toolbar with one button (Design Mode). You can
close this toolbar by clicking on the x.
4.) Right click the sheet tab and select View Code.
5.) Paste Mike's code onto the Code Module that is displayed.
6.) On the 6th line of code adjust the range to be the 1000 cells that you
would like to have the calendar linked to - Mike has assumed J1:J1000.
7.) Press Alt+Q to return to excel
8.) Select any cell OUTSIDE of the range you have specified. The calendar
should dissapear.
9.) Select any cell INSIDE the range you have specified. The calendar will
pop-up. Doubleclick on a date in the calendar and that date will appear in
the cell you selected. The calendar will remain visible until you click on
a cell outside of the range specified.

If I was implementing this for myself I would change the functionality
slightly so that only one click on the calendar is required and once a
date is selected the calendar is hidden - personal preference. To do this
right click the sheet tab again and select view code.

Replace these lines:

Private Sub Calendar1_DblClick()
ActiveCell.NumberFormat = "m/d/yyyy"
ActiveCell = Calendar1.Value
End Sub

With:

Private Sub Calendar1_Click()
ActiveCell.NumberFormat = "m/d/yyyy"
ActiveCell = Calendar1.Value
Calendar1.Visible = False
End Sub

Press Alt+Q again to return to excel.

Hope this helps
Rowan