Why dont you use a simple Userform?
Click on Cell B3 of the attached spreadsheet.
Private Sub SpinButton1_Change()
Range("A1").Value = SpinButton1.Value
TextBox1.Value = SpinButton1.Value
End Sub
Private Sub UserForm_Activate()
MArray = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
TextBox1.Value = Year(Date)
ComboBox1.List = MArray
t = Month(Date) - 1
ComboBox1.ListIndex = t
Temp = 5
SpinButton1.Value = 2016
MyDate = CDate("01/" & ComboBox1.Value & "/" & TextBox1.Value)
DaysMonth = Day(DateSerial(Year(MyDate), Month(MyDate) + 1, 1) - 1)
Mdate = Weekday(MyDate, vbSunday)
Daycount = 1
EndDate = Application.EoMonth(Weekday("01/" & ComboBox1.Value & "/" & TextBox1.Value, vbSunday), 0)
If Mdate <> 1 Then Daycount = 8 - Mdate
DayLoop:
ComboBox2.AddItem Daycount
Daycount = Daycount + 7
If Daycount < DaysMonth Then GoTo DayLoop
ComboBox2.ListIndex = 0
End Sub
Private Sub UserForm_Terminate()
Range("B3").Value = ComboBox2.Value & "/" & ComboBox1.Value & "/" & TextBox1.Value
End Sub
Bookmarks