-
Mar 23rd, 2010, 11:42 AM
#1
Thread Starter
Junior Member
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
-
Mar 23rd, 2010, 03:44 PM
#2
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
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
Mar 30th, 2010, 10:35 AM
#3
Thread Starter
Junior Member
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
-
Mar 30th, 2010, 03:51 PM
#4
Re: Excel VBA - SpinButton - 3rd Friday of every month
try fixing to correct friday like
vb Code:
y = 2010 m = 7 fstdate = Weekday(DateSerial(y, m, 1), vbFriday) s = DateSerial(y, m, (1 - fstdate) + 1) If Not Month(s) = m Then s = s + 7 s = s + 14 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
-
Apr 7th, 2010, 11:48 AM
#5
Thread Starter
Junior Member
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
-
Apr 8th, 2010, 05:56 AM
#6
Thread Starter
Junior Member
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
-
Apr 8th, 2010, 07:08 AM
#7
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|