Create a drop down calendar in a cell?
Hi,
Does anyone know a way to create a drop down calendar in a cell such that when the user clicks on the cell a calendar appears allowing them to select the required date. This date then appears in the cell. Is this possible or beyond the capabilities of excel? (NB I am using excel2000) Many thanks for your consideration of this problem --- Message posted from http://www.ExcelForum.com/ |
Create a drop down calendar in a cell?
Try this Ali
http://www.rondebruin.nl/calendar.htm -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "ali " wrote in message ... Hi, Does anyone know a way to create a drop down calendar in a cell such that when the user clicks on the cell a calendar appears allowing them to select the required date. This date then appears in the cell. Is this possible or beyond the capabilities of excel? (NB I am using excel2000) Many thanks for your consideration of this problem --- Message posted from http://www.ExcelForum.com/ |
Create a drop down calendar in a cell?
Many thanks for your help, however it is not quite working as i
hoped... I have followed the steps you gave me and now have a calendar on screen permanently. Ideally i want the calendar to be hidden until the user clicks on cell "c3" and to then disappear once the user has selected the desired date. The desired date will of course be left behind in cell c3. Any idea what i'm doing wrong? Many thanks --- Message posted from http://www.ExcelForum.com/ |
Create a drop down calendar in a cell?
If you only want to use it in C3 then use this
Private Sub Calendar1_DblClick() ActiveCell.NumberFormat = "m/d/yyyy" ActiveCell = Calendar1.Value ' This will hide it after you DblClick on a date Calendar1.Visible = False End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) ' this is the range where the SelectionChange is working ' I change it to c3 only If Not Application.Intersect(Range("c3"), Target) Is Nothing Then Calendar1.Left = Target.Left + Target.Width - Calendar1.Width Calendar1.Top = Target.Top + Target.Height Calendar1.Visible = True Else: Calendar1.Visible = False End If End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "ali " wrote in message ... Many thanks for your help, however it is not quite working as i hoped... I have followed the steps you gave me and now have a calendar on screen permanently. Ideally i want the calendar to be hidden until the user clicks on cell "c3" and to then disappear once the user has selected the desired date. The desired date will of course be left behind in cell c3. Any idea what i'm doing wrong? Many thanks --- Message posted from http://www.ExcelForum.com/ |
Create a drop down calendar in a cell?
Thanks again, i'm almost there now but not quite...
If i may, this is what i am trying to create: The sheet opens, a macro is run which creates a template (this is all working fine) The user then clicks on cell c3 which will bring up the calendar for the first time (the user will not see the calendar until they click or double click on c3) The user then selects the desired date and the calendar disappears. The date selected remains in cell c3. I apologise if this should be obvious - i am not yet as competant an excel user as i hope to become!!! Your help is very much appreciated --- Message posted from http://www.ExcelForum.com/ |
Create a drop down calendar in a cell?
Thanks Ron,
I didn't know Excel had a Calendar Control that you could add to a form. I was able to duplicate it all quite easily, after you pointed out the way. I found that the users may want to show the calendar form another way by double-clicking the activecell, by using this well-known programming code with two command buttons: Sub ShowTheCalendarDClick() ActiveSheet.OnDoubleClick = "ShowForm" End Sub Sub DontShowTheCalendarDClick() ActiveSheet.OnDoubleClick = "" End Sub Sub ShowForm() frmCalendar.Show End Sub -----Original Message----- Try this Ali http://www.rondebruin.nl/calendar.htm -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "ali " wrote in message ... Hi, Does anyone know a way to create a drop down calendar in a cell such that when the user clicks on the cell a calendar appears allowing them to select the required date. This date then appears in the cell. Is this possible or beyond the capabilities of excel? (NB I am using excel2000) Many thanks for your consideration of this problem --- Message posted from http://www.ExcelForum.com/ . |
Create a drop down calendar in a cell?
The code is doing what you want??
If you select c3 it will display the calendar If you double click on a date it will place this date in the cell and the calendar disappear -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "ali " wrote in message ... Thanks again, i'm almost there now but not quite... If i may, this is what i am trying to create: The sheet opens, a macro is run which creates a template (this is all working fine) The user then clicks on cell c3 which will bring up the calendar for the first time (the user will not see the calendar until they click or double click on c3) The user then selects the desired date and the calendar disappears. The date selected remains in cell c3. I apologise if this should be obvious - i am not yet as competant an excel user as i hope to become!!! Your help is very much appreciated --- Message posted from http://www.ExcelForum.com/ |
Create a drop down calendar in a cell?
Hi rick
If you want it in a userform see this link(also on my site) http://www.fontstuff.com/vba/vbatut07.htm You can add the userfom/code to your personal.xls this way you can use it in every workbook. -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Rick" wrote in message ... Thanks Ron, I didn't know Excel had a Calendar Control that you could add to a form. I was able to duplicate it all quite easily, after you pointed out the way. I found that the users may want to show the calendar form another way by double-clicking the activecell, by using this well-known programming code with two command buttons: Sub ShowTheCalendarDClick() ActiveSheet.OnDoubleClick = "ShowForm" End Sub Sub DontShowTheCalendarDClick() ActiveSheet.OnDoubleClick = "" End Sub Sub ShowForm() frmCalendar.Show End Sub -----Original Message----- Try this Ali http://www.rondebruin.nl/calendar.htm -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "ali " wrote in message ... Hi, Does anyone know a way to create a drop down calendar in a cell such that when the user clicks on the cell a calendar appears allowing them to select the required date. This date then appears in the cell. Is this possible or beyond the capabilities of excel? (NB I am using excel2000) Many thanks for your consideration of this problem --- Message posted from http://www.ExcelForum.com/ . |
All times are GMT +1. The time now is 02:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com