Results 1 to 6 of 6
  1. #1
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Fridays in Combo Box (VBA/Excel/2003)

    <font face="Comic Sans MS">It seemed so easy in theory ... I have a combo box where the user will choose which week they are updating a report for. The date in the box MUST be a Friday. I plan to fill it with 3 Fridays past, the current week, and 3 in the future.

    I need to determine the dates of these Fridays </font face=comic>
    Alan

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Fridays in Combo Box (VBA/Excel/2003)

    The attached workbook shows one approach. The row source for the combo box is a range of 7 cells on the worksheet.

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Fridays in Combo Box (VBA/Excel/2003)

    This is not directly responsive to your question, but I thought I'd post it anyway. I have an ASP application that lets users check their quarantined spam messages. The form lets them choose, among other options, "This week" with a checkbox to check to the previous Friday at 5PM. The code that calculates "this week" and rolls that back to the previous Friday at 5PM, if desired, is as follows:

    <code>datStart = DateAdd("d", -WeekDay(Date, vbMonday) + 1, Date) ' Monday at 12:00:00 AM
    strPeriod = " This Week"
    If Request.Form("chkWeekend") = "on" Then
    datStart = DateAdd("h", -55, datStart) ' Roll back 2 days and 7 hours
    strPeriod = strPeriod & " (including last weekend)"
    End If</code>

    Looking at it now, I can barely understand it (and it was just written over New Years!). So... maybe it doesn't help. <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

  4. #4
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Jeddah, Saudi Arabia
    Posts
    243
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Fridays in Combo Box (VBA/Excel/2003)

    Try this code:

    Dim today As Date, nextfriday As Date
    Dim dates(0 To 6) As Date
    Dim i As Integer

    today = Now
    nextfriday = today + (vbFriday - Weekday(today, 0) - 1)

    dates(0) = nextfriday - 21
    dates(1) = nextfriday - 14
    dates(2) = nextfriday - 7
    dates(3) = nextfriday
    dates(4) = nextfriday + 7
    dates(5) = nextfriday + 14
    dates(6) = nextfriday + 21

    For i = 0 To 6
    Debug.Print dates(i) & " - " & WeekdayName(Weekday(dates(i), 0))
    Next i
    End Sub

    Regards,
    Kevin Bell

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fridays in Combo Box (VBA/Excel/2003)

    > Roll back 2 days and 7 hours

    Nice! Would that it were so easy to do in real life .....

  6. #6
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Fridays in Combo Box (VBA/Excel/2003)

    What I finally ended with is:

    <pre> daDate = Date + 6 - Weekday(Date)

    Dates(0) = daDate <font color=red> 'Current Week</font color=red>
    Dates(1) = daDate - 21 <font color=red>'3 weeks ago</font color=red>
    Dates(2) = daDate - 14
    Dates(3) = daDate - 7
    Dates(4) = daDate + 7
    Dates(5) = daDate + 14
    Dates(6) = daDate + 21

    For W = 0 To 6
    .cmbReportDate.AddItem Application.Text(Dates(W), "YYYYMMDD") <font color=red>' Naming convention for reports</font color=red>
    Next W</pre>


    I need my users to pick the date for a Status Report that must be dated on Friday.

    Thanks everyone!
    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •