Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding Calendar to Worksheet
I want to add a calender to my worksheet that when a user selects a specific
cell the calender will come up, the user can select a date, and it will fill the cell with that date, then when the user goes to the next cell the calender dissapears, but the date stays in the specific cell. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding Calendar to Worksheet
If you do not already have the Visual Basic toolbar displayed, click
View/Toolbars from Excel's menu bar and select it from the list of Toolbars available. Next, click its Control Toolbox icon (looks like a wrench and hammer crossing each other). Next, click the More Controls icon on the Control Toolbox (also looks like a wrench and hammer crossing each other) and select Microsoft Month View Control from the list, then click on the worksheet to place it. Move it near the cell you want it to become visible for when selected. Right click the MonthView Control and select Properties from the popup list that appears. Set the Visible property to False. Also, you can control the size of the MonthView Control by changing its Font Size (click Font, then the ellipsis button). You can now dismiss the Properties window and double click the MonthView Control to get into the VBA editor. Copy/Paste this code in the code window that appeared... Private Sub MonthView1_DateClick(ByVal DateClicked As Date) Range("H5").Value = MonthView1.Value End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) MonthView1 .Visible = Target.Address = "$H$5" End Sub Note: Change the "H5" and "$H$5" example values to the cell address you want the functionality for (the addresses must be the same and the one in the SelectionChange event *must* be an absolute address. Go back to the worksheet and turn off Design Mode and dismiss the Control Toolbox (click their icons on the Visual Basic toolbar). That's it... it should work automatically from here on out. Rick "tracktor" wrote in message ... I want to add a calender to my worksheet that when a user selects a specific cell the calender will come up, the user can select a date, and it will fill the cell with that date, then when the user goes to the next cell the calender dissapears, but the date stays in the specific cell. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding Calendar to Worksheet
This did not work. I get a compile error. Invalid or unqualified reference.
Here is the code that I put in the VB Editor: Private Sub MonthView1_DateClick(ByVal DateClicked As Date) Range("H2").Value = MonthView1.Value End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) MonthView1 .Visible = Target.Address = "$H$2" End Sub This line is highlited yellow: Private Sub Worksheet_SelectionChange(ByVal Target As Range) "Rick Rothstein (MVP - VB)" wrote: If you do not already have the Visual Basic toolbar displayed, click View/Toolbars from Excel's menu bar and select it from the list of Toolbars available. Next, click its Control Toolbox icon (looks like a wrench and hammer crossing each other). Next, click the More Controls icon on the Control Toolbox (also looks like a wrench and hammer crossing each other) and select Microsoft Month View Control from the list, then click on the worksheet to place it. Move it near the cell you want it to become visible for when selected. Right click the MonthView Control and select Properties from the popup list that appears. Set the Visible property to False. Also, you can control the size of the MonthView Control by changing its Font Size (click Font, then the ellipsis button). You can now dismiss the Properties window and double click the MonthView Control to get into the VBA editor. Copy/Paste this code in the code window that appeared... Private Sub MonthView1_DateClick(ByVal DateClicked As Date) Range("H5").Value = MonthView1.Value End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) MonthView1 .Visible = Target.Address = "$H$5" End Sub Note: Change the "H5" and "$H$5" example values to the cell address you want the functionality for (the addresses must be the same and the one in the SelectionChange event *must* be an absolute address. Go back to the worksheet and turn off Design Mode and dismiss the Control Toolbox (click their icons on the Visual Basic toolbar). That's it... it should work automatically from here on out. Rick "tracktor" wrote in message ... I want to add a calender to my worksheet that when a user selects a specific cell the calender will come up, the user can select a date, and it will fill the cell with that date, then when the user goes to the next cell the calender dissapears, but the date stays in the specific cell. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding Calendar to Worksheet
See how Ron de Bruin does it.
http://www.rondebruin.nl/calendar.htm Gord Dibben MS Excel MVP On Sat, 17 May 2008 00:27:01 -0700, tracktor wrote: I want to add a calender to my worksheet that when a user selects a specific cell the calender will come up, the user can select a date, and it will fill the cell with that date, then when the user goes to the next cell the calender dissapears, but the date stays in the specific cell. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding Calendar to Worksheet
The control works on my system... perhaps the MonthView control came with my
version of compiled VB and is not usable unless you have a "legitimate" copy of compiled VB installed on your system (although I did not think that was the case). There is another calendar control available from the same More Tools icon in the Control Toolbox... it is called, appropriately enough, Calendar Control X.0 where on my system the X is 12... I'm assuming the version number changes with the version of Excel installed. You can follow all of the same instructions as I gave you earlier (except for the Font object sizing one... this control has separate Font objects for its Title and Day text, so you will need to change both of those in order to make the calendar look good at different sizes). As for code, it is basically the same... Private Sub Calendar1_Click() Range("H5").Value = Calendar1.Value End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Calendar1.Visible = Target.Address = "$H$5" End Sub Give that a try and see if it works for you. Rick "tracktor" wrote in message ... This did not work. I get a compile error. Invalid or unqualified reference. Here is the code that I put in the VB Editor: Private Sub MonthView1_DateClick(ByVal DateClicked As Date) Range("H2").Value = MonthView1.Value End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) MonthView1 .Visible = Target.Address = "$H$2" End Sub This line is highlited yellow: Private Sub Worksheet_SelectionChange(ByVal Target As Range) "Rick Rothstein (MVP - VB)" wrote: If you do not already have the Visual Basic toolbar displayed, click View/Toolbars from Excel's menu bar and select it from the list of Toolbars available. Next, click its Control Toolbox icon (looks like a wrench and hammer crossing each other). Next, click the More Controls icon on the Control Toolbox (also looks like a wrench and hammer crossing each other) and select Microsoft Month View Control from the list, then click on the worksheet to place it. Move it near the cell you want it to become visible for when selected. Right click the MonthView Control and select Properties from the popup list that appears. Set the Visible property to False. Also, you can control the size of the MonthView Control by changing its Font Size (click Font, then the ellipsis button). You can now dismiss the Properties window and double click the MonthView Control to get into the VBA editor. Copy/Paste this code in the code window that appeared... Private Sub MonthView1_DateClick(ByVal DateClicked As Date) Range("H5").Value = MonthView1.Value End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) MonthView1 .Visible = Target.Address = "$H$5" End Sub Note: Change the "H5" and "$H$5" example values to the cell address you want the functionality for (the addresses must be the same and the one in the SelectionChange event *must* be an absolute address. Go back to the worksheet and turn off Design Mode and dismiss the Control Toolbox (click their icons on the Visual Basic toolbar). That's it... it should work automatically from here on out. Rick "tracktor" wrote in message ... I want to add a calender to my worksheet that when a user selects a specific cell the calender will come up, the user can select a date, and it will fill the cell with that date, then when the user goes to the next cell the calender dissapears, but the date stays in the specific cell. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding Calendar to Worksheet
... (except for the Font object sizing one... this control has separate
Font objects for its Title and Day text... Just noticed... it also has a font object for the Grid text also. Rick |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding Calendar to Worksheet
As far as I know my copy of Microsoft Office is legitamate, I bought it form
the Universitys bookstore, and it is Microsoft Professional Edition 2003, and it says €śAcademic Price, Not for use in a commercial environment.€ť As far as I know it is a full blown version that was sold at a reduced price for students. I put the Calendar Control 11..0 and it works fine on an empty worksheet. But when I put it on the worksheet with the other code that you gave me for €śemptying the contents of a cell based on the value of another cell€ť post. I get a message that says €ś Compile error: Ambiguous name detected: Worksheet_SelectionChange This is exactly what is pasted in the View Code window before I pasted the code for the calendar: '********************** START OF CODE ********************** Dim PreviousValue As String Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$H$233" Then If Range("H233").Value = "See page 10 Additional Terms" And Range("H235").Value = "0" And _ PreviousValue = "Enter Specific COE Date" Then Range("H235").Value = "" End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$H$233" Then PreviousValue = Target.Value End Sub '*********************** END OF CODE *********************** This is what it looks like after I pasted the code for the calendar. '********************** START OF CODE ********************** Dim PreviousValue As String Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$H$233" Then If Range("H233").Value = "See page 10 Additional Terms" And Range("H235").Value = "0" And _ PreviousValue = "Enter Specific COE Date" Then Range("H235").Value = "" End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$H$233" Then PreviousValue = Target.Value End Sub '*********************** END OF CODE *********************** Private Sub Calendar1_Click() Range("H2").Value = Calendar1.Value End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Calendar1.Visible = Target.Address = "$H$2" End Sub "Rick Rothstein (MVP - VB)" wrote: ... (except for the Font object sizing one... this control has separate Font objects for its Title and Day text... Just noticed... it also has a font object for the Grid text also. Rick |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding Calendar to Worksheet
You cannot have two events of the same type in the same worksheet module.
Looks like you are trying for too many Selection_Changes. Also looks like too many Worksheet_Changes Gord Dibben MS Excel MVP On Sat, 17 May 2008 14:55:01 -0700, tracktor wrote: As far as I know my copy of Microsoft Office is legitamate, I bought it form the University’s bookstore, and it is Microsoft Professional Edition 2003, and it says “Academic Price, Not for use in a commercial environment.” As far as I know it is a full blown version that was sold at a reduced price for students. I put the Calendar Control 11..0 and it works fine on an empty worksheet. But when I put it on the worksheet with the other code that you gave me for “emptying the contents of a cell based on the value of another cell” post. I get a message that says “ Compile error: Ambiguous name detected: Worksheet_SelectionChange This is exactly what is pasted in the View Code window before I pasted the code for the calendar: '********************** START OF CODE ********************** Dim PreviousValue As String Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$H$233" Then If Range("H233").Value = "See page 10 Additional Terms" And Range("H235").Value = "0" And _ PreviousValue = "Enter Specific COE Date" Then Range("H235").Value = "" End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$H$233" Then PreviousValue = Target.Value End Sub '*********************** END OF CODE *********************** This is what it looks like after I pasted the code for the calendar. '********************** START OF CODE ********************** Dim PreviousValue As String Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$H$233" Then If Range("H233").Value = "See page 10 Additional Terms" And Range("H235").Value = "0" And _ PreviousValue = "Enter Specific COE Date" Then Range("H235").Value = "" End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$H$233" Then PreviousValue = Target.Value End Sub '*********************** END OF CODE *********************** Private Sub Calendar1_Click() Range("H2").Value = Calendar1.Value End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Calendar1.Visible = Target.Address = "$H$2" End Sub "Rick Rothstein (MVP - VB)" wrote: ... (except for the Font object sizing one... this control has separate Font objects for its Title and Day text... Just noticed... it also has a font object for the Grid text also. Rick |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding Calendar to Worksheet
I have more than one place to enter a date, so I need a calendar to show up
in those places when the user selects that cell. I also have one of them that I want the cell to be emptied based on the value of another cell. I'm sure this could be done. It's obvious I don't know how to code. For right now when a user selects cells H2, H59, and H213, I need a calendar to pop up so they can select a date, and as soon as they do I need the calendar to dissapear. All three of the dates are different. The date in cell H213 needs to be emptied based on the value of another cell. "Gord Dibben" wrote: You cannot have two events of the same type in the same worksheet module. Looks like you are trying for too many Selection_Changes. Also looks like too many Worksheet_Changes Gord Dibben MS Excel MVP On Sat, 17 May 2008 14:55:01 -0700, tracktor wrote: As far as I know my copy of Microsoft Office is legitamate, I bought it form the Universitys bookstore, and it is Microsoft Professional Edition 2003, and it says €śAcademic Price, Not for use in a commercial environment.€ť As far as I know it is a full blown version that was sold at a reduced price for students. I put the Calendar Control 11..0 and it works fine on an empty worksheet. But when I put it on the worksheet with the other code that you gave me for €śemptying the contents of a cell based on the value of another cell€ť post. I get a message that says €ś Compile error: Ambiguous name detected: Worksheet_SelectionChange This is exactly what is pasted in the View Code window before I pasted the code for the calendar: '********************** START OF CODE ********************** Dim PreviousValue As String Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$H$233" Then If Range("H233").Value = "See page 10 Additional Terms" And Range("H235").Value = "0" And _ PreviousValue = "Enter Specific COE Date" Then Range("H235").Value = "" End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$H$233" Then PreviousValue = Target.Value End Sub '*********************** END OF CODE *********************** This is what it looks like after I pasted the code for the calendar. '********************** START OF CODE ********************** Dim PreviousValue As String Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$H$233" Then If Range("H233").Value = "See page 10 Additional Terms" And Range("H235").Value = "0" And _ PreviousValue = "Enter Specific COE Date" Then Range("H235").Value = "" End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$H$233" Then PreviousValue = Target.Value End Sub '*********************** END OF CODE *********************** Private Sub Calendar1_Click() Range("H2").Value = Calendar1.Value End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Calendar1.Visible = Target.Address = "$H$2" End Sub "Rick Rothstein (MVP - VB)" wrote: ... (except for the Font object sizing one... this control has separate Font objects for its Title and Day text... Just noticed... it also has a font object for the Grid text also. Rick |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding Calendar to Worksheet
See
http://www.rondebruin.nl/calendar.htm 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 You can add a if statement between this if do do something different for a specific cell -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "tracktor" wrote in message ... I have more than one place to enter a date, so I need a calendar to show up in those places when the user selects that cell. I also have one of them that I want the cell to be emptied based on the value of another cell. I'm sure this could be done. It's obvious I don't know how to code. For right now when a user selects cells H2, H59, and H213, I need a calendar to pop up so they can select a date, and as soon as they do I need the calendar to dissapear. All three of the dates are different. The date in cell H213 needs to be emptied based on the value of another cell. "Gord Dibben" wrote: You cannot have two events of the same type in the same worksheet module. Looks like you are trying for too many Selection_Changes. Also looks like too many Worksheet_Changes Gord Dibben MS Excel MVP On Sat, 17 May 2008 14:55:01 -0700, tracktor wrote: As far as I know my copy of Microsoft Office is legitamate, I bought it form the Universitys bookstore, and it is Microsoft Professional Edition 2003, and it says €śAcademic Price, Not for use in a commercial environment.€ť As far as I know it is a full blown version that was sold at a reduced price for students. I put the Calendar Control 11..0 and it works fine on an empty worksheet. But when I put it on the worksheet with the other code that you gave me for €śemptying the contents of a cell based on the value of another cell€ť post. I get a message that says €ś Compile error: Ambiguous name detected: Worksheet_SelectionChange This is exactly what is pasted in the View Code window before I pasted the code for the calendar: '********************** START OF CODE ********************** Dim PreviousValue As String Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$H$233" Then If Range("H233").Value = "See page 10 Additional Terms" And Range("H235").Value = "0" And _ PreviousValue = "Enter Specific COE Date" Then Range("H235").Value = "" End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$H$233" Then PreviousValue = Target.Value End Sub '*********************** END OF CODE *********************** This is what it looks like after I pasted the code for the calendar. '********************** START OF CODE ********************** Dim PreviousValue As String Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$H$233" Then If Range("H233").Value = "See page 10 Additional Terms" And Range("H235").Value = "0" And _ PreviousValue = "Enter Specific COE Date" Then Range("H235").Value = "" End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$H$233" Then PreviousValue = Target.Value End Sub '*********************** END OF CODE *********************** Private Sub Calendar1_Click() Range("H2").Value = Calendar1.Value End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Calendar1.Visible = Target.Address = "$H$2" End Sub "Rick Rothstein (MVP - VB)" wrote: ... (except for the Font object sizing one... this control has separate Font objects for its Title and Day text... Just noticed... it also has a font object for the Grid text also. Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding a yearly calendar to excel spreadsheet | Excel Worksheet Functions | |||
Adding a Drop-Down Calendar | Excel Discussion (Misc queries) | |||
Adding absences in rolling twelvemonths in a calendar | Excel Discussion (Misc queries) | |||
Worksheet object (Calendar) | Excel Discussion (Misc queries) | |||
adding letters in a payroll calendar | Excel Discussion (Misc queries) |