Hi all,
I am trying to populate a combobox for timesheet application with all week
ending dates for our accounting year. Our year runs from April 1st - March
31st.

Code below shows where I have got to so far and works in part. However, I
can't figure out how to stop the code after the end of the accounting year
month.

variable values are obtained as follows:
Startweek value is the next timesheet date in fomat dd/mm/yyyy
This value would start at first week ending date for accounting year and is
incremented by 1 week each time timsheet is submitted.

yearendmonth value is the month / year date accounting period ends in format
dd/mm/yyyy this value is taken from an excel spreadsheet calendar which I
update each year.

How would i need to alter the code to show all week ending dates for each
month in the accounting year only?
Hope I have made myself clear - if someone could kindly assist me with this
please, I would be most grateful.


Private Sub UserForm_Initialize()
Dim startweek As Date
Dim yearendmonth As Date
startweek = ThisWorkbook.Worksheets("Timesheet").Range("K8").V alue
yearendmonth =
ThisWorkbook.Worksheets("YearlyCalendar").Range("S 36").Value

hyear = Year(yearendmonth)

With ComboBox1
.Clear
NextDate = DateValue(startweek)
Do
.AddItem Format(NextDate, "dd/mm/yyyy")
WeekDate = NextDate
IntervalType = "ww"
Number = 1

'change week ending to next sunday
NextDate = DateAdd(IntervalType, Number, WeekDate)

Loop Until Year(NextDate) hyear
.Text = startweek
End With
End Sub
--
JB

Change this line

Loop Until Year(NextDate) hyear

to

Loop Until NextDate yearendmonth

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"johnboy" wrote in message
...
Hi all,
I am trying to populate a combobox for timesheet application with all week
ending dates for our accounting year. Our year runs from April 1st - March
31st.

Code below shows where I have got to so far and works in part. However, I
can't figure out how to stop the code after the end of the accounting year
month.

variable values are obtained as follows:
Startweek value is the next timesheet date in fomat dd/mm/yyyy
This value would start at first week ending date for accounting year and
is
incremented by 1 week each time timsheet is submitted.

yearendmonth value is the month / year date accounting period ends in
format
dd/mm/yyyy this value is taken from an excel spreadsheet calendar which I
update each year.

How would i need to alter the code to show all week ending dates for each
month in the accounting year only?
Hope I have made myself clear - if someone could kindly assist me with
this
please, I would be most grateful.


Private Sub UserForm_Initialize()
Dim startweek As Date
Dim yearendmonth As Date
startweek = ThisWorkbook.Worksheets("Timesheet").Range("K8").V alue
yearendmonth =
ThisWorkbook.Worksheets("YearlyCalendar").Range("S 36").Value

hyear = Year(yearendmonth)

With ComboBox1
.Clear
NextDate = DateValue(startweek)
Do
.AddItem Format(NextDate, "dd/mm/yyyy")
WeekDate = NextDate
IntervalType = "ww"
Number = 1

'change week ending to next sunday
NextDate = DateAdd(IntervalType, Number, WeekDate)

Loop Until Year(NextDate) hyear
.Text = startweek
End With
End Sub
--
JB

Bob,
thanks for reply - I forgot to mention that accounting year will be changing
to Jan - Dec & in this case change you mention does not work. How can I adapt
code to take this in to consideration?
--
JB


"Bob Phillips" wrote:

Change this line

Loop Until Year(NextDate) hyear

to

Loop Until NextDate yearendmonth

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"johnboy" wrote in message
...
Hi all,
I am trying to populate a combobox for timesheet application with all week
ending dates for our accounting year. Our year runs from April 1st - March
31st.

Code below shows where I have got to so far and works in part. However, I
can't figure out how to stop the code after the end of the accounting year
month.

variable values are obtained as follows:
Startweek value is the next timesheet date in fomat dd/mm/yyyy
This value would start at first week ending date for accounting year and
is
incremented by 1 week each time timsheet is submitted.

yearendmonth value is the month / year date accounting period ends in
format
dd/mm/yyyy this value is taken from an excel spreadsheet calendar which I
update each year.

How would i need to alter the code to show all week ending dates for each
month in the accounting year only?
Hope I have made myself clear - if someone could kindly assist me with
this
please, I would be most grateful.


Private Sub UserForm_Initialize()
Dim startweek As Date
Dim yearendmonth As Date
startweek = ThisWorkbook.Worksheets("Timesheet").Range("K8").V alue
yearendmonth =
ThisWorkbook.Worksheets("YearlyCalendar").Range("S 36").Value

hyear = Year(yearendmonth)

With ComboBox1
.Clear
NextDate = DateValue(startweek)
Do
.AddItem Format(NextDate, "dd/mm/yyyy")
WeekDate = NextDate
IntervalType = "ww"
Number = 1

'change week ending to next sunday
NextDate = DateAdd(IntervalType, Number, WeekDate)

Loop Until Year(NextDate) hyear
.Text = startweek
End With
End Sub
--
JB

Why not? It all depends upon the correct start and end dates, and is
automatic from there, nothing in the code.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"johnboy" wrote in message
...
Bob,
thanks for reply - I forgot to mention that accounting year will be
changing
to Jan - Dec & in this case change you mention does not work. How can I
adapt
code to take this in to consideration?
--
JB


"Bob Phillips" wrote:

Change this line

Loop Until Year(NextDate) hyear

to

Loop Until NextDate yearendmonth

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"johnboy" wrote in message
...
Hi all,
I am trying to populate a combobox for timesheet application with all
week
ending dates for our accounting year. Our year runs from April 1st -
March
31st.

Code below shows where I have got to so far and works in part.
However, I
can't figure out how to stop the code after the end of the accounting
year
month.

variable values are obtained as follows:
Startweek value is the next timesheet date in fomat dd/mm/yyyy
This value would start at first week ending date for accounting year
and
is
incremented by 1 week each time timsheet is submitted.

yearendmonth value is the month / year date accounting period ends in
format
dd/mm/yyyy this value is taken from an excel spreadsheet calendar
which I
update each year.

How would i need to alter the code to show all week ending dates for
each
month in the accounting year only?
Hope I have made myself clear - if someone could kindly assist me with
this
please, I would be most grateful.


Private Sub UserForm_Initialize()
Dim startweek As Date
Dim yearendmonth As Date
startweek = ThisWorkbook.Worksheets("Timesheet").Range("K8").V alue
yearendmonth =
ThisWorkbook.Worksheets("YearlyCalendar").Range("S 36").Value

hyear = Year(yearendmonth)

With ComboBox1
.Clear
NextDate = DateValue(startweek)
Do
.AddItem Format(NextDate, "dd/mm/yyyy")
WeekDate = NextDate
IntervalType = "ww"
Number = 1

'change week ending to next sunday
NextDate = DateAdd(IntervalType, Number, WeekDate)

Loop Until Year(NextDate) hyear
.Text = startweek
End With
End Sub
--
JB

Bob,
thanks for response - your solution did work, but whilst awaiting replies, i
had made some changes which upset the result.
I have now solved problem by using DateDiff - this counts all the week
ending dates needed to populate combobox using either Apr - Mar or Jan - Dec
accounting periods.
--
JB


"Bob Phillips" wrote:

Why not? It all depends upon the correct start and end dates, and is
automatic from there, nothing in the code.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"johnboy" wrote in message
...
Bob,
thanks for reply - I forgot to mention that accounting year will be
changing
to Jan - Dec & in this case change you mention does not work. How can I
adapt
code to take this in to consideration?
--
JB


"Bob Phillips" wrote:

Change this line

Loop Until Year(NextDate) hyear

to

Loop Until NextDate yearendmonth

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"johnboy" wrote in message
...
Hi all,
I am trying to populate a combobox for timesheet application with all
week
ending dates for our accounting year. Our year runs from April 1st -
March
31st.

Code below shows where I have got to so far and works in part.
However, I
can't figure out how to stop the code after the end of the accounting
year
month.

variable values are obtained as follows:
Startweek value is the next timesheet date in fomat dd/mm/yyyy
This value would start at first week ending date for accounting year
and
is
incremented by 1 week each time timsheet is submitted.

yearendmonth value is the month / year date accounting period ends in
format
dd/mm/yyyy this value is taken from an excel spreadsheet calendar
which I
update each year.

How would i need to alter the code to show all week ending dates for
each
month in the accounting year only?
Hope I have made myself clear - if someone could kindly assist me with
this
please, I would be most grateful.


Private Sub UserForm_Initialize()
Dim startweek As Date
Dim yearendmonth As Date
startweek = ThisWorkbook.Worksheets("Timesheet").Range("K8").V alue
yearendmonth =
ThisWorkbook.Worksheets("YearlyCalendar").Range("S 36").Value

hyear = Year(yearendmonth)

With ComboBox1
.Clear
NextDate = DateValue(startweek)
Do
.AddItem Format(NextDate, "dd/mm/yyyy")
WeekDate = NextDate
IntervalType = "ww"
Number = 1

'change week ending to next sunday
NextDate = DateAdd(IntervalType, Number, WeekDate)

Loop Until Year(NextDate) hyear
.Text = startweek
End With
End Sub
--
JB