how to pre define what to show on the top of the Combo Box

  • Thread starter Thread starter BJ
  • Start date Start date
B

BJ

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.
 
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.
 
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?
 
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
 
Back
Top