| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
pick calendar date and day
I've figured out how to add a calendar control to my excel 2007 spreadsheet.
For example, when I click in cell F8, the calendar appears, I click on a November 11, the date of 11/11/08 gets entered into cell F8, and the calendar disappears. It works great. However, what I would like to be able to set up is when the person clicks on November 11, the date of November gets entered into cell F8 AND the corresponding weekday (in this case, Tuesday) gets automatically entered into the cell above, F7. The calendar control is set up for a range of cells: F8:L8. I would want the weekdays set up to be entered into the range of cells: F7:L8. Is this possible? Thanks. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
pick calendar date and day
G'day
Try this: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("F8:L8")) Is Nothing Then If DTPicker1.Visible Then DTPicker1.Visible = False End If If Not Intersect(Target, Range("F8:L8")) Is Nothing Then With ActiveCell .Offset(0, -1).Value = Target.Value End With End If End Sub Format Cells F7:L7 as Custom "ddd" for eg Mon, Tue, Wed, etc....... Format Cells F7:L7 as Custom "dddd" for eg Monday, Tueday, etc....... (Remove quotes from ddd & dddd) HTH Mark. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
pick calendar date and day
I think this worksheet Change event code will do what you want...
Private Sub Worksheet_Change(ByVal Target As Range) Dim X As Long If Target.Address = "$F$8" Then Target.Offset(-1).Value = Format(Target.Value, "dddd") For X = 1 To 6 Target.Offset(, X).Value = Target.Value + X Target.Offset(-1, X).Value = Format(Target.Value + X, "dddd") Next End If End Sub The "dddd" argument in the two Format statements makes the function return the fully spelled out day name; if you wanted the abbreviated day name, then just change the "dddd" to "ddd". -- Rick (MVP - Excel) "hlock" wrote in message ... I've figured out how to add a calendar control to my excel 2007 spreadsheet. For example, when I click in cell F8, the calendar appears, I click on a November 11, the date of 11/11/08 gets entered into cell F8, and the calendar disappears. It works great. However, what I would like to be able to set up is when the person clicks on November 11, the date of November gets entered into cell F8 AND the corresponding weekday (in this case, Tuesday) gets automatically entered into the cell above, F7. The calendar control is set up for a range of cells: F8:L8. I would want the weekdays set up to be entered into the range of cells: F7:L8. Is this possible? Thanks. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
pick calendar date and day
Thank you. This is sort of what I was looking for. However, I want each
date/day to be independent of each other. When someone fills in F8 with a date (and thus the day in F7), I don't want the rest of the range to be automatically filled in. For example, I pick Oct 15 for F8, Oct 30 for G8 and Nov 4 for H8. Then F7 would automatically fill in with Wednesday, G7 would fill in with Thursday and H7 would fill in with Tuesday. Thanks!! "Rick Rothstein" wrote: I think this worksheet Change event code will do what you want... Private Sub Worksheet_Change(ByVal Target As Range) Dim X As Long If Target.Address = "$F$8" Then Target.Offset(-1).Value = Format(Target.Value, "dddd") For X = 1 To 6 Target.Offset(, X).Value = Target.Value + X Target.Offset(-1, X).Value = Format(Target.Value + X, "dddd") Next End If End Sub The "dddd" argument in the two Format statements makes the function return the fully spelled out day name; if you wanted the abbreviated day name, then just change the "dddd" to "ddd". -- Rick (MVP - Excel) "hlock" wrote in message ... I've figured out how to add a calendar control to my excel 2007 spreadsheet. For example, when I click in cell F8, the calendar appears, I click on a November 11, the date of 11/11/08 gets entered into cell F8, and the calendar disappears. It works great. However, what I would like to be able to set up is when the person clicks on November 11, the date of November gets entered into cell F8 AND the corresponding weekday (in this case, Tuesday) gets automatically entered into the cell above, F7. The calendar control is set up for a range of cells: F8:L8. I would want the weekdays set up to be entered into the range of cells: F7:L8. Is this possible? Thanks. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
pick calendar date and day
Then try this worksheet Change event code instead...
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("F8:L8")) Is Nothing Then Target.Offset(-1).Value = Format(Target.Value, "did") End If End Sub -- Rick (MVP - Excel) "hlock" wrote in message ... Thank you. This is sort of what I was looking for. However, I want each date/day to be independent of each other. When someone fills in F8 with a date (and thus the day in F7), I don't want the rest of the range to be automatically filled in. For example, I pick Oct 15 for F8, Oct 30 for G8 and Nov 4 for H8. Then F7 would automatically fill in with Wednesday, G7 would fill in with Thursday and H7 would fill in with Tuesday. Thanks!! "Rick Rothstein" wrote: I think this worksheet Change event code will do what you want... Private Sub Worksheet_Change(ByVal Target As Range) Dim X As Long If Target.Address = "$F$8" Then Target.Offset(-1).Value = Format(Target.Value, "dddd") For X = 1 To 6 Target.Offset(, X).Value = Target.Value + X Target.Offset(-1, X).Value = Format(Target.Value + X, "dddd") Next End If End Sub The "dddd" argument in the two Format statements makes the function return the fully spelled out day name; if you wanted the abbreviated day name, then just change the "dddd" to "ddd". -- Rick (MVP - Excel) "hlock" wrote in message ... I've figured out how to add a calendar control to my excel 2007 spreadsheet. For example, when I click in cell F8, the calendar appears, I click on a November 11, the date of 11/11/08 gets entered into cell F8, and the calendar disappears. It works great. However, what I would like to be able to set up is when the person clicks on November 11, the date of November gets entered into cell F8 AND the corresponding weekday (in this case, Tuesday) gets automatically entered into the cell above, F7. The calendar control is set up for a range of cells: F8:L8. I would want the weekdays set up to be entered into the range of cells: F7:L8. Is this possible? Thanks. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
pick calendar date and day
Perfect. Thank you!
"Rick Rothstein" wrote: Then try this worksheet Change event code instead... Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("F8:L8")) Is Nothing Then Target.Offset(-1).Value = Format(Target.Value, "did") End If End Sub -- Rick (MVP - Excel) "hlock" wrote in message ... Thank you. This is sort of what I was looking for. However, I want each date/day to be independent of each other. When someone fills in F8 with a date (and thus the day in F7), I don't want the rest of the range to be automatically filled in. For example, I pick Oct 15 for F8, Oct 30 for G8 and Nov 4 for H8. Then F7 would automatically fill in with Wednesday, G7 would fill in with Thursday and H7 would fill in with Tuesday. Thanks!! "Rick Rothstein" wrote: I think this worksheet Change event code will do what you want... Private Sub Worksheet_Change(ByVal Target As Range) Dim X As Long If Target.Address = "$F$8" Then Target.Offset(-1).Value = Format(Target.Value, "dddd") For X = 1 To 6 Target.Offset(, X).Value = Target.Value + X Target.Offset(-1, X).Value = Format(Target.Value + X, "dddd") Next End If End Sub The "dddd" argument in the two Format statements makes the function return the fully spelled out day name; if you wanted the abbreviated day name, then just change the "dddd" to "ddd". -- Rick (MVP - Excel) "hlock" wrote in message ... I've figured out how to add a calendar control to my excel 2007 spreadsheet. For example, when I click in cell F8, the calendar appears, I click on a November 11, the date of 11/11/08 gets entered into cell F8, and the calendar disappears. It works great. However, what I would like to be able to set up is when the person clicks on November 11, the date of November gets entered into cell F8 AND the corresponding weekday (in this case, Tuesday) gets automatically entered into the cell above, F7. The calendar control is set up for a range of cells: F8:L8. I would want the weekdays set up to be entered into the range of cells: F7:L8. Is this possible? Thanks. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
pick calendar date and day
You are welcome. I guess you picked up on the "did" instead of "dddd" typo?
-- Rick (MVP - Excel) "hlock" wrote in message ... Perfect. Thank you! "Rick Rothstein" wrote: Then try this worksheet Change event code instead... Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("F8:L8")) Is Nothing Then Target.Offset(-1).Value = Format(Target.Value, "did") End If End Sub -- Rick (MVP - Excel) "hlock" wrote in message ... Thank you. This is sort of what I was looking for. However, I want each date/day to be independent of each other. When someone fills in F8 with a date (and thus the day in F7), I don't want the rest of the range to be automatically filled in. For example, I pick Oct 15 for F8, Oct 30 for G8 and Nov 4 for H8. Then F7 would automatically fill in with Wednesday, G7 would fill in with Thursday and H7 would fill in with Tuesday. Thanks!! "Rick Rothstein" wrote: I think this worksheet Change event code will do what you want... Private Sub Worksheet_Change(ByVal Target As Range) Dim X As Long If Target.Address = "$F$8" Then Target.Offset(-1).Value = Format(Target.Value, "dddd") For X = 1 To 6 Target.Offset(, X).Value = Target.Value + X Target.Offset(-1, X).Value = Format(Target.Value + X, "dddd") Next End If End Sub The "dddd" argument in the two Format statements makes the function return the fully spelled out day name; if you wanted the abbreviated day name, then just change the "dddd" to "ddd". -- Rick (MVP - Excel) "hlock" wrote in message ... I've figured out how to add a calendar control to my excel 2007 spreadsheet. For example, when I click in cell F8, the calendar appears, I click on a November 11, the date of 11/11/08 gets entered into cell F8, and the calendar disappears. It works great. However, what I would like to be able to set up is when the person clicks on November 11, the date of November gets entered into cell F8 AND the corresponding weekday (in this case, Tuesday) gets automatically entered into the cell above, F7. The calendar control is set up for a range of cells: F8:L8. I would want the weekdays set up to be entered into the range of cells: F7:L8. Is this possible? Thanks. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Pick a date from a cell drop down calendar | Excel Discussion (Misc queries) | |||
| how do i pick A date for a cell from a drop down calendar? | Excel Discussion (Misc queries) | |||
| pick date from calendar in excel | Excel Worksheet Functions | |||
| Populate a cell with a date using calendar pick | Excel Discussion (Misc queries) | |||
| have a calendar pop up in a cell to pick & insert a date in excel | Excel Worksheet Functions | |||
Linear Mode
