If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#31
|
|||
|
|||
ho do i insert calendar into xls worksheet
Hi Darren
You can use a macro and assign it to a button if you want Delete the selection change event and use this in the sheet module Private Sub Calendar1_Click() ActiveCell.Value = CDbl(Calendar1.Value) ActiveCell.NumberFormat = "mm/dd/yyyy" ActiveCell.Select Calendar1.Visible = False End Sub Copy this in a normal module Sub Call_Calendar() With Sheets("Sheet1") If Not Application.Intersect(.Range("A1:A10"), ActiveCell) Is Nothing Then .Calendar1.Left = ActiveCell.Left + ActiveCell.Width - .Calendar1.Width .Calendar1.Top = ActiveCell.Top + ActiveCell.Height .Calendar1.Visible = True ' select Today's date in the Calendar .Calendar1.Value = Date ElseIf .Calendar1.Visible Then .Calendar1.Visible = False End If End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Darren" wrote in message ... Hi Ron, Ignore my previous reply - I've managed to fix the problem of the date - i didn't see the fomat within the code.. Thanks anyway But do you know if there is a way of when you select the cell and a date, just one click on the date to remove the calender rather than selecting another cell or pressing enter and is there an icon you can use..? (pretty much similar to a web based calendar form..?) Thanks in advance.. "Ron de Bruin" wrote: Hi Darren Which Excel version do you use ? You have a protected sheet ? -- Regards Ron de Bruin http://www.rondebruin.nl "Darren" wrote in message ... Hi Ron, Thanks for displaying the information on how to load a calendar. I have a couple of questions if you can help please? Is there a way of when you click on the cell the calendar pops up to only select the date once via the mouse and the pop up calendar disappears rather than you clicking enter or clicking on other cell? Also I am having problems changing the date format to UK - I have changed this in the format cells section, but it reverts back to US version for each date selected? - Is there any way you can lock the format? Thanks "Ron de Bruin" wrote: Hi Drahos Maybe the Calendar you have add to the worksheet is not named Calendar1 Check out the name -- Regards Ron de Bruin http://www.rondebruin.nl "Drahos" wrote in message ... Hi Paul, I need to use in my Excel Form a calender as described. I have done what was recommeded on the webside you are reffering to. While choosing a cell from the range where should be the calendar I receive the Runtime error "424" - object required and the Debug shows this line: Calendar1.Left = Target.Left + Target.Width - Calendar1.Width If choosing a cell outside of the range I receive the same runtime error and the Debug shows this line: ElseIf Calendar1.Visible Then Calendar1.Visible = False I use the Excel 2003. Please, can you advice me what the problem is. Thanks a lot. "Paul B" wrote: halina, here is one way, http://www.rondebruin.nl/calendar.htm -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "halina" wrote in message ... |
#32
|
|||
|
|||
ho do i insert calendar into xls worksheet
How do you tell what the calendar is named?
I tried just using calendar.... but that didn't work either. I folled the instructions on this page: http://www.fontstuff.com/vba/vbatut07.htm & then I inserted this code on the sheet: Private Sub Calendar1_Click() ActiveCell.Value = CDbl(Calendar1.Value) ActiveCell.NumberFormat = "mm/dd/yyyy" ActiveCell.Select End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Not Application.Intersect(Range("A1:A20"), 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 .. "Ron de Bruin" wrote: Hi Drahos Maybe the Calendar you have add to the worksheet is not named Calendar1 Check out the name -- Regards Ron de Bruin http://www.rondebruin.nl "Drahos" wrote in message ... Hi Paul, I need to use in my Excel Form a calender as described. I have done what was recommeded on the webside you are reffering to. While choosing a cell from the range where should be the calendar I receive the Runtime error "424" - object required and the Debug shows this line: Calendar1.Left = Target.Left + Target.Width - Calendar1.Width If choosing a cell outside of the range I receive the same runtime error and the Debug shows this line: ElseIf Calendar1.Visible Then Calendar1.Visible = False I use the Excel 2003. Please, can you advice me what the problem is. Thanks a lot. "Paul B" wrote: halina, here is one way, http://www.rondebruin.nl/calendar.htm -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "halina" wrote in message ... |
#33
|
|||
|
|||
ho do i insert calendar into xls worksheet
Hi
When you use the code from my page you only have to add the calendar to the worksheet with Insert Object Read the page again http://www.rondebruin.nl/calendar.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "tmirelle" wrote in message ... How do you tell what the calendar is named? I tried just using calendar.... but that didn't work either. I folled the instructions on this page: http://www.fontstuff.com/vba/vbatut07.htm & then I inserted this code on the sheet: Private Sub Calendar1_Click() ActiveCell.Value = CDbl(Calendar1.Value) ActiveCell.NumberFormat = "mm/dd/yyyy" ActiveCell.Select End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Not Application.Intersect(Range("A1:A20"), 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 . "Ron de Bruin" wrote: Hi Drahos Maybe the Calendar you have add to the worksheet is not named Calendar1 Check out the name -- Regards Ron de Bruin http://www.rondebruin.nl "Drahos" wrote in message ... Hi Paul, I need to use in my Excel Form a calender as described. I have done what was recommeded on the webside you are reffering to. While choosing a cell from the range where should be the calendar I receive the Runtime error "424" - object required and the Debug shows this line: Calendar1.Left = Target.Left + Target.Width - Calendar1.Width If choosing a cell outside of the range I receive the same runtime error and the Debug shows this line: ElseIf Calendar1.Visible Then Calendar1.Visible = False I use the Excel 2003. Please, can you advice me what the problem is. Thanks a lot. "Paul B" wrote: halina, here is one way, http://www.rondebruin.nl/calendar.htm -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "halina" wrote in message ... |
#34
|
|||
|
|||
ho do i insert calendar into xls worksheet
I am trying to get the calendar to pop up everytime you are in the date
column... to help the user get it in in the right format. Is that possible? "Ron de Bruin" wrote: Hi When you use the code from my page you only have to add the calendar to the worksheet with Insert Object Read the page again http://www.rondebruin.nl/calendar.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "tmirelle" wrote in message ... How do you tell what the calendar is named? I tried just using calendar.... but that didn't work either. I folled the instructions on this page: http://www.fontstuff.com/vba/vbatut07.htm & then I inserted this code on the sheet: Private Sub Calendar1_Click() ActiveCell.Value = CDbl(Calendar1.Value) ActiveCell.NumberFormat = "mm/dd/yyyy" ActiveCell.Select End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Not Application.Intersect(Range("A1:A20"), 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 . "Ron de Bruin" wrote: Hi Drahos Maybe the Calendar you have add to the worksheet is not named Calendar1 Check out the name -- Regards Ron de Bruin http://www.rondebruin.nl "Drahos" wrote in message ... Hi Paul, I need to use in my Excel Form a calender as described. I have done what was recommeded on the webside you are reffering to. While choosing a cell from the range where should be the calendar I receive the Runtime error "424" - object required and the Debug shows this line: Calendar1.Left = Target.Left + Target.Width - Calendar1.Width If choosing a cell outside of the range I receive the same runtime error and the Debug shows this line: ElseIf Calendar1.Visible Then Calendar1.Visible = False I use the Excel 2003. Please, can you advice me what the problem is. Thanks a lot. "Paul B" wrote: halina, here is one way, http://www.rondebruin.nl/calendar.htm -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "halina" wrote in message ... |
#35
|
|||
|
|||
ho do i insert calendar into xls worksheet
Change the range in the code to the range with your date cells
See also this note on my site Note: you can use this if your range is not one area If Not Application.Intersect(Range("A1:A20,C1,E1"), Target) Is Nothing Then -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "tmirelle" wrote in message ... I am trying to get the calendar to pop up everytime you are in the date column... to help the user get it in in the right format. Is that possible? "Ron de Bruin" wrote: Hi When you use the code from my page you only have to add the calendar to the worksheet with Insert Object Read the page again http://www.rondebruin.nl/calendar.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "tmirelle" wrote in message ... How do you tell what the calendar is named? I tried just using calendar.... but that didn't work either. I folled the instructions on this page: http://www.fontstuff.com/vba/vbatut07.htm & then I inserted this code on the sheet: Private Sub Calendar1_Click() ActiveCell.Value = CDbl(Calendar1.Value) ActiveCell.NumberFormat = "mm/dd/yyyy" ActiveCell.Select End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Not Application.Intersect(Range("A1:A20"), 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 . "Ron de Bruin" wrote: Hi Drahos Maybe the Calendar you have add to the worksheet is not named Calendar1 Check out the name -- Regards Ron de Bruin http://www.rondebruin.nl "Drahos" wrote in message ... Hi Paul, I need to use in my Excel Form a calender as described. I have done what was recommeded on the webside you are reffering to. While choosing a cell from the range where should be the calendar I receive the Runtime error "424" - object required and the Debug shows this line: Calendar1.Left = Target.Left + Target.Width - Calendar1.Width If choosing a cell outside of the range I receive the same runtime error and the Debug shows this line: ElseIf Calendar1.Visible Then Calendar1.Visible = False I use the Excel 2003. Please, can you advice me what the problem is. Thanks a lot. "Paul B" wrote: halina, here is one way, http://www.rondebruin.nl/calendar.htm -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "halina" wrote in message ... |
#36
|
|||
|
|||
ho do i insert calendar into xls worksheet
Still won't work for me, I'm not sure how to identify the range (it's column A)
But clicking anywhere in the sheet after I have added your code to the worksheet gives me error 424. & highlights the line: Calendar1.Left = Target.Left + Target.Width - Calendar1.Width I don't know what I am doing wrong. "Ron de Bruin" wrote: Change the range in the code to the range with your date cells See also this note on my site Note: you can use this if your range is not one area If Not Application.Intersect(Range("A1:A20,C1,E1"), Target) Is Nothing Then -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "tmirelle" wrote in message ... I am trying to get the calendar to pop up everytime you are in the date column... to help the user get it in in the right format. Is that possible? "Ron de Bruin" wrote: Hi When you use the code from my page you only have to add the calendar to the worksheet with Insert Object Read the page again http://www.rondebruin.nl/calendar.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "tmirelle" wrote in message ... How do you tell what the calendar is named? I tried just using calendar.... but that didn't work either. I folled the instructions on this page: http://www.fontstuff.com/vba/vbatut07.htm & then I inserted this code on the sheet: Private Sub Calendar1_Click() ActiveCell.Value = CDbl(Calendar1.Value) ActiveCell.NumberFormat = "mm/dd/yyyy" ActiveCell.Select End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Not Application.Intersect(Range("A1:A20"), 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 . "Ron de Bruin" wrote: Hi Drahos Maybe the Calendar you have add to the worksheet is not named Calendar1 Check out the name -- Regards Ron de Bruin http://www.rondebruin.nl "Drahos" wrote in message ... Hi Paul, I need to use in my Excel Form a calender as described. I have done what was recommeded on the webside you are reffering to. While choosing a cell from the range where should be the calendar I receive the Runtime error "424" - object required and the Debug shows this line: Calendar1.Left = Target.Left + Target.Width - Calendar1.Width If choosing a cell outside of the range I receive the same runtime error and the Debug shows this line: ElseIf Calendar1.Visible Then Calendar1.Visible = False I use the Excel 2003. Please, can you advice me what the problem is. Thanks a lot. "Paul B" wrote: halina, here is one way, http://www.rondebruin.nl/calendar.htm -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "halina" wrote in message ... |
#37
|
|||
|
|||
ho do i insert calendar into xls worksheet
Calendar1 is the name of your calendar
When you are in Edit mode you can see the name in the formulabar on the left Be sure that it is named Calendar1 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "tmirelle" wrote in message news Still won't work for me, I'm not sure how to identify the range (it's column A) But clicking anywhere in the sheet after I have added your code to the worksheet gives me error 424. & highlights the line: Calendar1.Left = Target.Left + Target.Width - Calendar1.Width I don't know what I am doing wrong. "Ron de Bruin" wrote: Change the range in the code to the range with your date cells See also this note on my site Note: you can use this if your range is not one area If Not Application.Intersect(Range("A1:A20,C1,E1"), Target) Is Nothing Then -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "tmirelle" wrote in message ... I am trying to get the calendar to pop up everytime you are in the date column... to help the user get it in in the right format. Is that possible? "Ron de Bruin" wrote: Hi When you use the code from my page you only have to add the calendar to the worksheet with Insert Object Read the page again http://www.rondebruin.nl/calendar.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "tmirelle" wrote in message ... How do you tell what the calendar is named? I tried just using calendar.... but that didn't work either. I folled the instructions on this page: http://www.fontstuff.com/vba/vbatut07.htm & then I inserted this code on the sheet: Private Sub Calendar1_Click() ActiveCell.Value = CDbl(Calendar1.Value) ActiveCell.NumberFormat = "mm/dd/yyyy" ActiveCell.Select End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Not Application.Intersect(Range("A1:A20"), 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 . "Ron de Bruin" wrote: Hi Drahos Maybe the Calendar you have add to the worksheet is not named Calendar1 Check out the name -- Regards Ron de Bruin http://www.rondebruin.nl "Drahos" wrote in message ... Hi Paul, I need to use in my Excel Form a calender as described. I have done what was recommeded on the webside you are reffering to. While choosing a cell from the range where should be the calendar I receive the Runtime error "424" - object required and the Debug shows this line: Calendar1.Left = Target.Left + Target.Width - Calendar1.Width If choosing a cell outside of the range I receive the same runtime error and the Debug shows this line: ElseIf Calendar1.Visible Then Calendar1.Visible = False I use the Excel 2003. Please, can you advice me what the problem is. Thanks a lot. "Paul B" wrote: halina, here is one way, http://www.rondebruin.nl/calendar.htm -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "halina" wrote in message ... |
#38
|
|||
|
|||
ho do i insert calendar into xls worksheet
Calendar1 is the name of your calendar
When you are in Edit mode you can see and edit the name in the formulabar on the left Be sure that it is named Calendar1 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "tmirelle" wrote in message news Still won't work for me, I'm not sure how to identify the range (it's column A) But clicking anywhere in the sheet after I have added your code to the worksheet gives me error 424. & highlights the line: Calendar1.Left = Target.Left + Target.Width - Calendar1.Width I don't know what I am doing wrong. "Ron de Bruin" wrote: Change the range in the code to the range with your date cells See also this note on my site Note: you can use this if your range is not one area If Not Application.Intersect(Range("A1:A20,C1,E1"), Target) Is Nothing Then -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "tmirelle" wrote in message ... I am trying to get the calendar to pop up everytime you are in the date column... to help the user get it in in the right format. Is that possible? "Ron de Bruin" wrote: Hi When you use the code from my page you only have to add the calendar to the worksheet with Insert Object Read the page again http://www.rondebruin.nl/calendar.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "tmirelle" wrote in message ... How do you tell what the calendar is named? I tried just using calendar.... but that didn't work either. I folled the instructions on this page: http://www.fontstuff.com/vba/vbatut07.htm & then I inserted this code on the sheet: Private Sub Calendar1_Click() ActiveCell.Value = CDbl(Calendar1.Value) ActiveCell.NumberFormat = "mm/dd/yyyy" ActiveCell.Select End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Not Application.Intersect(Range("A1:A20"), 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 . "Ron de Bruin" wrote: Hi Drahos Maybe the Calendar you have add to the worksheet is not named Calendar1 Check out the name -- Regards Ron de Bruin http://www.rondebruin.nl "Drahos" wrote in message ... Hi Paul, I need to use in my Excel Form a calender as described. I have done what was recommeded on the webside you are reffering to. While choosing a cell from the range where should be the calendar I receive the Runtime error "424" - object required and the Debug shows this line: Calendar1.Left = Target.Left + Target.Width - Calendar1.Width If choosing a cell outside of the range I receive the same runtime error and the Debug shows this line: ElseIf Calendar1.Visible Then Calendar1.Visible = False I use the Excel 2003. Please, can you advice me what the problem is. Thanks a lot. "Paul B" wrote: halina, here is one way, http://www.rondebruin.nl/calendar.htm -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "halina" wrote in message ... |
#39
|
|||
|
|||
ho do i insert calendar into xls worksheet
Thanks, but still no matter where I click in the sheet I get the same
error 424 & highlights the line: Calendar1.Left = Target.Left + Target.Width - Calendar1.Width "Ron de Bruin" wrote: Calendar1 is the name of your calendar When you are in Edit mode you can see and edit the name in the formulabar on the left Be sure that it is named Calendar1 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "tmirelle" wrote in message news Still won't work for me, I'm not sure how to identify the range (it's column A) But clicking anywhere in the sheet after I have added your code to the worksheet gives me error 424. & highlights the line: Calendar1.Left = Target.Left + Target.Width - Calendar1.Width I don't know what I am doing wrong. "Ron de Bruin" wrote: Change the range in the code to the range with your date cells See also this note on my site Note: you can use this if your range is not one area If Not Application.Intersect(Range("A1:A20,C1,E1"), Target) Is Nothing Then -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "tmirelle" wrote in message ... I am trying to get the calendar to pop up everytime you are in the date column... to help the user get it in in the right format. Is that possible? "Ron de Bruin" wrote: Hi When you use the code from my page you only have to add the calendar to the worksheet with Insert Object Read the page again http://www.rondebruin.nl/calendar.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "tmirelle" wrote in message ... How do you tell what the calendar is named? I tried just using calendar.... but that didn't work either. I folled the instructions on this page: http://www.fontstuff.com/vba/vbatut07.htm & then I inserted this code on the sheet: Private Sub Calendar1_Click() ActiveCell.Value = CDbl(Calendar1.Value) ActiveCell.NumberFormat = "mm/dd/yyyy" ActiveCell.Select End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Not Application.Intersect(Range("A1:A20"), 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 . "Ron de Bruin" wrote: Hi Drahos Maybe the Calendar you have add to the worksheet is not named Calendar1 Check out the name -- Regards Ron de Bruin http://www.rondebruin.nl "Drahos" wrote in message ... Hi Paul, I need to use in my Excel Form a calender as described. I have done what was recommeded on the webside you are reffering to. While choosing a cell from the range where should be the calendar I receive the Runtime error "424" - object required and the Debug shows this line: Calendar1.Left = Target.Left + Target.Width - Calendar1.Width If choosing a cell outside of the range I receive the same runtime error and the Debug shows this line: ElseIf Calendar1.Visible Then Calendar1.Visible = False I use the Excel 2003. Please, can you advice me what the problem is. Thanks a lot. "Paul B" wrote: halina, here is one way, http://www.rondebruin.nl/calendar.htm -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "halina" wrote in message ... |
#40
|
|||
|
|||
ho do i insert calendar into xls worksheet
Close Excel
Start Excel again and try the code from my site in a new workbook -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "tmirelle" wrote in message news Thanks, but still no matter where I click in the sheet I get the same error 424 & highlights the line: Calendar1.Left = Target.Left + Target.Width - Calendar1.Width "Ron de Bruin" wrote: Calendar1 is the name of your calendar When you are in Edit mode you can see and edit the name in the formulabar on the left Be sure that it is named Calendar1 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "tmirelle" wrote in message news Still won't work for me, I'm not sure how to identify the range (it's column A) But clicking anywhere in the sheet after I have added your code to the worksheet gives me error 424. & highlights the line: Calendar1.Left = Target.Left + Target.Width - Calendar1.Width I don't know what I am doing wrong. "Ron de Bruin" wrote: Change the range in the code to the range with your date cells See also this note on my site Note: you can use this if your range is not one area If Not Application.Intersect(Range("A1:A20,C1,E1"), Target) Is Nothing Then -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "tmirelle" wrote in message ... I am trying to get the calendar to pop up everytime you are in the date column... to help the user get it in in the right format. Is that possible? "Ron de Bruin" wrote: Hi When you use the code from my page you only have to add the calendar to the worksheet with Insert Object Read the page again http://www.rondebruin.nl/calendar.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "tmirelle" wrote in message ... How do you tell what the calendar is named? I tried just using calendar.... but that didn't work either. I folled the instructions on this page: http://www.fontstuff.com/vba/vbatut07.htm & then I inserted this code on the sheet: Private Sub Calendar1_Click() ActiveCell.Value = CDbl(Calendar1.Value) ActiveCell.NumberFormat = "mm/dd/yyyy" ActiveCell.Select End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Not Application.Intersect(Range("A1:A20"), 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 . "Ron de Bruin" wrote: Hi Drahos Maybe the Calendar you have add to the worksheet is not named Calendar1 Check out the name -- Regards Ron de Bruin http://www.rondebruin.nl "Drahos" wrote in message ... Hi Paul, I need to use in my Excel Form a calender as described. I have done what was recommeded on the webside you are reffering to. While choosing a cell from the range where should be the calendar I receive the Runtime error "424" - object required and the Debug shows this line: Calendar1.Left = Target.Left + Target.Width - Calendar1.Width If choosing a cell outside of the range I receive the same runtime error and the Debug shows this line: ElseIf Calendar1.Visible Then Calendar1.Visible = False I use the Excel 2003. Please, can you advice me what the problem is. Thanks a lot. "Paul B" wrote: halina, here is one way, http://www.rondebruin.nl/calendar.htm -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "halina" wrote in message ... |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Insert Picture Error | Lynda | Powerpoint | 19 | November 22nd, 2005 09:00 PM |
can you insert a comment into a protected worksheet? | TKGerdie | Worksheet Functions | 3 | October 18th, 2005 04:32 PM |
can I insert a calendar page into a Publisher brochure template? | Cheska99 | Publisher | 3 | September 22nd, 2005 09:26 PM |
Insert worksheet | Krish | General Discussion | 2 | July 12th, 2004 01:22 AM |
auto insert copy of worksheet | Bernie Deitrick | Worksheet Functions | 0 | March 4th, 2004 02:18 PM |