Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Task calendars from spreadsheet info | Setting up and Configuration of Excel | |||
update calendars in Excel? | Excel Discussion (Misc queries) | |||
Excel Calendars | Excel Discussion (Misc queries) | |||
can i use formulas in calendars in excel? | Excel Programming | |||
Inserting Navigatable Calendars into Excel | Excel Discussion (Misc queries) |