Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to pre define what to show on the top of the Combo Box
the combo box's lisefillrange is from a range that contains 52 items which
are 52 Sundays from prior and current yesrs. when user clicks on the drop-down arrow, how to show the first Sunday of the current year not the first Sunday from the prior year. For example, the Combo Box contains all the Sundays from 2008 and 2009, when user clicks on it, the first item shows in the list is 1/4/09 not '1/6/08'. thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to pre define what to show on the top of the Combo Box
You cxan do ti either from the wroksheet or VBA. One way is to put the
formula below into A1 to get 1st sunday =DATE(YEAR(TODAY()),1,1)+MOD(8-WEEKDAY(DATE(YEAR(TODAY()),1,1)),7) The in A2 put in =A1+7 to get the next sunday Then copy B2 down the column to row 52 The formula above does the following DATE(YEAR(TODAY()),1,1) gets January 1st. If X is Jan 1st Mod(8 - weekday(X),7) gets the number of days from Jan 1 to the 1st Sunday. weekday equals the following Sunday = 1 Monday = 2 Tuesday = 3 Wednesday = 4 Thurdays = 5 Friday = 6 Saturday = 7 So if the 1st is Friday then 8 - 6 = 2 is the number of days From Friday to Sunday The MOD function is needed if the 1st is on Sunday since 8 - 1 = 7 will give you the 2nd sunday instead of the 1st Sunday. So the final formula is X + Mod(8 - weekday(X),7) where X is Jan 1st. "BJ" wrote: the combo box's lisefillrange is from a range that contains 52 items which are 52 Sundays from prior and current yesrs. when user clicks on the drop-down arrow, how to show the first Sunday of the current year not the first Sunday from the prior year. For example, the Combo Box contains all the Sundays from 2008 and 2009, when user clicks on it, the first item shows in the list is 1/4/09 not '1/6/08'. thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to pre define what to show on the top of the Combo Box
I guess i didn't write clearly in my questions.
my question is: in an Excel worksheet, there is a combo box contains 52 Sundays in it including prior year's (2008) and current year's (2009). when user clicks on the combo box, it always shows the top one in the list - 1/8/2008. for most of the time, user needs to scrool down to get the 2009's Sundays. So are there any ways that when user clicks on the drop-down, 1/4/09 shows on the top? "Joel" wrote: You cxan do ti either from the wroksheet or VBA. One way is to put the formula below into A1 to get 1st sunday =DATE(YEAR(TODAY()),1,1)+MOD(8-WEEKDAY(DATE(YEAR(TODAY()),1,1)),7) The in A2 put in =A1+7 to get the next sunday Then copy B2 down the column to row 52 The formula above does the following DATE(YEAR(TODAY()),1,1) gets January 1st. If X is Jan 1st Mod(8 - weekday(X),7) gets the number of days from Jan 1 to the 1st Sunday. weekday equals the following Sunday = 1 Monday = 2 Tuesday = 3 Wednesday = 4 Thurdays = 5 Friday = 6 Saturday = 7 So if the 1st is Friday then 8 - 6 = 2 is the number of days From Friday to Sunday The MOD function is needed if the 1st is on Sunday since 8 - 1 = 7 will give you the 2nd sunday instead of the 1st Sunday. So the final formula is X + Mod(8 - weekday(X),7) where X is Jan 1st. "BJ" wrote: the combo box's lisefillrange is from a range that contains 52 items which are 52 Sundays from prior and current yesrs. when user clicks on the drop-down arrow, how to show the first Sunday of the current year not the first Sunday from the prior year. For example, the Combo Box contains all the Sundays from 2008 and 2009, when user clicks on it, the first item shows in the list is 1/4/09 not '1/6/08'. thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to pre define what to show on the top of the Combo Box
You need to set the listindex in an event macro like a workbook open event.
You can put in the macro the listindex number of 1/4/09 (the item number starting at 0) or use the code below to find the first data in 2009. Reading/Writing a combobox with a date return the serial number of the date (1 = jan 1, 1900) and not the actual text date. I need to format the date to text before I could write the actual date back to the combobox. With Sheets("Sheet1").ComboBox1 Numitems = .ListCount For i = 0 To (Numitems - 1) ListYear = Year(.List(i)) If ListYear = Year(Date) Then .Text = Format(.List(i), "MM/DD/YY") Exit For End If Next i End With "BJ" wrote: I guess i didn't write clearly in my questions. my question is: in an Excel worksheet, there is a combo box contains 52 Sundays in it including prior year's (2008) and current year's (2009). when user clicks on the combo box, it always shows the top one in the list - 1/8/2008. for most of the time, user needs to scrool down to get the 2009's Sundays. So are there any ways that when user clicks on the drop-down, 1/4/09 shows on the top? "Joel" wrote: You cxan do ti either from the wroksheet or VBA. One way is to put the formula below into A1 to get 1st sunday =DATE(YEAR(TODAY()),1,1)+MOD(8-WEEKDAY(DATE(YEAR(TODAY()),1,1)),7) The in A2 put in =A1+7 to get the next sunday Then copy B2 down the column to row 52 The formula above does the following DATE(YEAR(TODAY()),1,1) gets January 1st. If X is Jan 1st Mod(8 - weekday(X),7) gets the number of days from Jan 1 to the 1st Sunday. weekday equals the following Sunday = 1 Monday = 2 Tuesday = 3 Wednesday = 4 Thurdays = 5 Friday = 6 Saturday = 7 So if the 1st is Friday then 8 - 6 = 2 is the number of days From Friday to Sunday The MOD function is needed if the 1st is on Sunday since 8 - 1 = 7 will give you the 2nd sunday instead of the 1st Sunday. So the final formula is X + Mod(8 - weekday(X),7) where X is Jan 1st. "BJ" wrote: the combo box's lisefillrange is from a range that contains 52 items which are 52 Sundays from prior and current yesrs. when user clicks on the drop-down arrow, how to show the first Sunday of the current year not the first Sunday from the prior year. For example, the Combo Box contains all the Sundays from 2008 and 2009, when user clicks on it, the first item shows in the list is 1/4/09 not '1/6/08'. thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Show full Combo List at once | Excel Programming | |||
Combo Box, show multiple columns | Excel Discussion (Misc queries) | |||
How do I find excel names that do not show in the define function | Excel Discussion (Misc queries) | |||
Combo Box - Show only first two digits? | Excel Discussion (Misc queries) | |||
Define Range for Combo Box | Excel Programming |