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.