Excel VBA - SpinButton - 3rd Friday of every month-VBForums
Results 1 to 7 of 7

Thread: Excel VBA - SpinButton - 3rd Friday of every month

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Feb 2010
    Posts
    20

    Excel VBA - SpinButton - 3rd Friday of every month

    Hi,

    I am trying to draw a spin button on a userform, in order to generate the date of the 3rd friday of each month that is after the current date (today). In order words, the coming 3rd friday will be the earliest date available.

    For example, the date of every 3rd friday of the month will be shown with every incremental change of the spinbutton as below:

    16/4/2010
    21/5/2010
    18/6/2010
    16/7/2010

    However, the 19/3/2010 should not be given since it has already over and on 17/4/2010, the earliest date will be replaced by 21/5/2010 as 16/4/2010 will become unavailable.

    It would be great if you could help me up with this as i'm new to spin button. Should i predefine a list of date or is there any excel formula that can help to identify them?

    Thank you

    xwxc

  2. #2
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,928

    Re: Excel VBA - SpinButton - 3rd Friday of every month

    Hi xwxc230302

    Whenever you are stuck, I would always recommend you to take a piece of paper and write down the logic on how you would tackle the problem. Create a logic and then simply convert the same into the code. For example

    The function should take today's date and a futuristic date as parameters.

    something like

    Code:
    Private Function ThirdFridays(StartDate As Date, enddate As Date)
        
        
    End Function
    This is what I got when I implemented the suggestion that I gave above...

    BASIC LOGIC
    1) Get the First and Last date of the month from today's date
    2) Loop through the First date and the Last date and find the 3rd Friday. If the 3rd Friday's date is greater than today's date then store it in an array and exit the loop
    3) Add one date to the last date that we got in step two and from that date get the last date. Repeat the loop and search for the 3rd Friday. If the 3rd Friday is less than the end date that you have mentioned in your function then add it to the array and move to the next month and so on so forth.
    4) Once your array is filled then simply use that to display on each increment/decrease of spinner button.

    Give it a try... If you get stuck then simply post the code that you have tried and we will help you...

    Note: There can be many logic on how you achieve what you want... You might come up with a different logic... it's fine... simply convert that to code and see how it works out
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Feb 2010
    Posts
    20

    Re: Excel VBA - SpinButton - 3rd Friday of every month

    Hi,

    Many thanks for your guidance, i managed to write the code that almost return a result that i wanted.
    I have 2 issues that i couldn't figure out the solution. Here is the code:

    Private Sub SpinButton1_Change()

    Dim FstDate As Date
    Dim Link_Cell As Integer
    Dim ExpCell As Range

    Set ExpCell = Sheets("Sheet1").Range("F2")
    Link_Cell = Range("D2").Value

    FstDate = DateSerial(Year(Date), Month(Date) + Link_Cell, 1)
    ExpCell.Value = (FstDate - Weekday(FstDate, 1)) + 20

    End Sub

    1st: I'd like to set the value of link_cell with the value of spinbutton1.linkedcell.value - this doesn't work
    e.g. set link_cell = spinbutton1.linkedcell.value
    (I refer the linkedcell to range"D2" in the spinbutton property)

    2nd: While the code returns the date of 3rd friday of the month based on the value increased by spinbutton, it gives the incorrect date when the 1st day of the month starts with Saturday. For example, it returns 14/5/2010, but not 21/5/2010 as 1st May falls on Saturday. Same issue with Jan 2011 (14/1/2011 given instead of 21/1/2011).

    Could you please advice how to solve these issues?

    thank you

    xwxc

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    19,992

    Re: Excel VBA - SpinButton - 3rd Friday of every month

    try fixing to correct friday like
    vb Code:
    1. y = 2010
    2. m = 7
    3. fstdate = Weekday(DateSerial(y, m, 1), vbFriday)
    4. s = DateSerial(y, m, (1 - fstdate) + 1)
    5. If Not Month(s) = m Then s = s + 7
    6. s = s + 14
    7. ExpCell = s
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Feb 2010
    Posts
    20

    Re: Excel VBA - SpinButton - 3rd Friday of every month

    Hi,

    Thanks, i managed to modify my code

    Private Sub SpinButton1_Change()

    Dim FstDate, ExpDate As Date
    Dim DayNo, Link_Cell As Integer
    Dim ExpCell As Range

    Set ExpCell = Sheets("Sheet1").Range("F2")
    Link_Cell = Range("D2").Value


    FstDate = DateSerial(Year(Date), Month(Date) + Link_Cell, 1)
    ExpDate = FstDate - Weekday(FstDate, 1)

    If Weekday(FstDate) = 7 Then
    ExpCell.Value = DateAdd("d", 27, ExpDate)
    Else
    ExpCell.Value = DateAdd("d", 20, ExpDate)
    End If

    End Sub


    However, i still cant solve the 1st issue in my previous post.
    In my code, i refer my linkcell to this:
    Link_Cell = Range("D2").Value
    But is there a way to refer it directly to the spinbutton property? something like this:
    Link_Cell = spinbutton1.linkedcell.value??

    Thanks

    xwxc

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Feb 2010
    Posts
    20

    Re: Excel VBA - SpinButton - 3rd Friday of every month

    Hi,

    I have another question on spin button. The date function doesn't seem to work in the spinbutton code as an error pops out saying "can't find project or library".
    For example, i have a simple code:

    Private Sub ExpiryDate_Button_Change()

    range("M44").value = Date

    End Sub

    I would appreciate it you could explain where i did wrong?

    Thanks

    xwxc

  7. #7
    PowerPoster
    Join Date
    Dec 2004
    Posts
    19,992

    Re: Excel VBA - SpinButton - 3rd Friday of every month

    check for missing references
    menu > tools > references
    uncheck any that say they are missing
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.