Drop down box Help

[Guest]

Hi all,

I have a form combo box (drop down 1) on my Excel sheet which contain date
values, and there is also a Link cell which contain week number of the date
selected in drop down box.

Sample values in Drop down and link cell.

Date Week.
01/01/2005 01
08/01/2005 02
15/01/2005 03
22/01/2005 04

Assuming my file will be opened on 01,08,15 and 22 of every month. When the
Excel file is opened, I would like to display current date and week number in
the drop down box and link cell.

How do I do this. Please help.

Many thanks
Bhaskar.

 

[Bob Phillips]

Try this in your code

Combobox1.Listindex = WeekNum(Date)-1

Function WeekNum(inDate)
WeekNum = _
Int(((inDate - DateSerial(Year(inDate), 1, 1)) / 7)) + 1 _
+ IIf(Weekday(DateSerial(Year(inDate), 1, 1)) > _
Weekday(inDate), 1, 0)
End Function

 

[Guest]

=today() in cell A1
cell a2 =1&"/"month(a1)&"/"year(A1)
cell A3 =8&"/"month(a1)&"/"year(A1)
cell A4 =15&"/"month(a1)&"/"year(A1)
cellA5 = 22&"/"month(a1)&"/"year(A1)
your range for your dropdown is A2:A5
I am not sure about the cell link tho,it will always show 1,2,3or 4.You
could create another cell =weeknum(A1,1) which shows the number of the week
in the year.the second argument in week num is for when the week
starts,monday (2)or sunday(1)