date in combo formatted as number
 

The subject doesn't do this justice. I have a combo box for the user to
select a year from the current year to 12 in the future. The ListFillRange
for the combo box refers to a sheet with the following formulas:

AD
1 Year
2 =TODAY() - All dates are formatted as YYYY
3 =DATE(YEAR(AD2)+1,MONTH(AD2),DAY(AD2))
4 =DATE(YEAR(AD3)+1,MONTH(AD3),DAY(AD3))
5 ....etc....

Everything works great...you push the button at the side of the combo box
and it drops down and shows a list of years starting from the current year.
No problems until a year is selected. i.e. if 2006 is selected it returns
38991. I know this has to do with the formulas I am using in the
spreadsheet. But I don't know how to fix it.

I used the formulas as a way to not have to update the program every year.
I want the list to start at 2006 when we hit 1/1/2006.

Thank you for the help.

date in combo formatted as number
 

yr = Year(date)
for i = 0 to 12
Combobox1.AddItem yr + i
Next

--
Regards,
Tom Ogilvy

"JNW" wrote in message
...
The subject doesn't do this justice. I have a combo box for the user to
select a year from the current year to 12 in the future. The
ListFillRange
for the combo box refers to a sheet with the following formulas:

AD
1 Year
2 =TODAY() - All dates are formatted as YYYY
3 =DATE(YEAR(AD2)+1,MONTH(AD2),DAY(AD2))
4 =DATE(YEAR(AD3)+1,MONTH(AD3),DAY(AD3))
5 ....etc....

Everything works great...you push the button at the side of the combo box
and it drops down and shows a list of years starting from the current
year.
No problems until a year is selected. i.e. if 2006 is selected it returns
38991. I know this has to do with the formulas I am using in the
spreadsheet. But I don't know how to fix it.

I used the formulas as a way to not have to update the program every year.
I want the list to start at 2006 when we hit 1/1/2006.

Thank you for the help.

date in combo formatted as number
 

Thank you for the reply Tom.

Excuse my ignorance, but where would I place the code you have suggested?

"Tom Ogilvy" wrote:

yr = Year(date)
for i = 0 to 12
Combobox1.AddItem yr + i
Next

--
Regards,
Tom Ogilvy

"JNW" wrote in message
...
The subject doesn't do this justice. I have a combo box for the user to
select a year from the current year to 12 in the future. The
ListFillRange
for the combo box refers to a sheet with the following formulas:

AD
1 Year
2 =TODAY() - All dates are formatted as YYYY
3 =DATE(YEAR(AD2)+1,MONTH(AD2),DAY(AD2))
4 =DATE(YEAR(AD3)+1,MONTH(AD3),DAY(AD3))
5 ....etc....

Everything works great...you push the button at the side of the combo box
and it drops down and shows a list of years starting from the current
year.
No problems until a year is selected. i.e. if 2006 is selected it returns
38991. I know this has to do with the formulas I am using in the
spreadsheet. But I don't know how to fix it.

I used the formulas as a way to not have to update the program every year.
I want the list to start at 2006 when we hit 1/1/2006.

Thank you for the help.

date in combo formatted as number
 

okay, I tried putting your code in the userform_activate sub and got error
number 70 'permission denied'. My file is not read-only. the code stopped
at the following line:
Combobox1.AddItem yr + i

Any ideas?


"Tom Ogilvy" wrote:

yr = Year(date)
for i = 0 to 12
Combobox1.AddItem yr + i
Next

--
Regards,
Tom Ogilvy

"JNW" wrote in message
...
The subject doesn't do this justice. I have a combo box for the user to
select a year from the current year to 12 in the future. The
ListFillRange
for the combo box refers to a sheet with the following formulas:

AD
1 Year
2 =TODAY() - All dates are formatted as YYYY
3 =DATE(YEAR(AD2)+1,MONTH(AD2),DAY(AD2))
4 =DATE(YEAR(AD3)+1,MONTH(AD3),DAY(AD3))
5 ....etc....

Everything works great...you push the button at the side of the combo box
and it drops down and shows a list of years starting from the current
year.
No problems until a year is selected. i.e. if 2006 is selected it returns
38991. I know this has to do with the formulas I am using in the
spreadsheet. But I don't know how to fix it.

I used the formulas as a way to not have to update the program every year.
I want the list to start at 2006 when we hit 1/1/2006.

Thank you for the help.

date in combo formatted as number
 

The error was my fault. I forgot to remove the range from the listfillrange
property.

However, when I select a year it returns that year minus 100 (i.e. 2005 is
selected it returns 1905)

"Tom Ogilvy" wrote:

yr = Year(date)
for i = 0 to 12
Combobox1.AddItem yr + i
Next

--
Regards,
Tom Ogilvy

"JNW" wrote in message
...
The subject doesn't do this justice. I have a combo box for the user to
select a year from the current year to 12 in the future. The
ListFillRange
for the combo box refers to a sheet with the following formulas:

AD
1 Year
2 =TODAY() - All dates are formatted as YYYY
3 =DATE(YEAR(AD2)+1,MONTH(AD2),DAY(AD2))
4 =DATE(YEAR(AD3)+1,MONTH(AD3),DAY(AD3))
5 ....etc....

Everything works great...you push the button at the side of the combo box
and it drops down and shows a list of years starting from the current
year.
No problems until a year is selected. i.e. if 2006 is selected it returns
38991. I know this has to do with the formulas I am using in the
spreadsheet. But I don't know how to fix it.

I used the formulas as a way to not have to update the program every year.
I want the list to start at 2006 when we hit 1/1/2006.

Thank you for the help.

date in combo formatted as number
 

You need to Dim your variables correctly with dates. Try this. I used it in
the worksheet activate event, but you can put it in a regular code module.

Option Explicit

Private Sub Worksheet_Activate()
Dim yr As String
Dim i As Long
yr = Year(Date)
ComboBox1.Clear 'clears out old dates and linked cell
For i = 0 To 11
ComboBox1.AddItem yr + i
Next
End Sub

Mike F
"JNW" wrote in message
...
The error was my fault. I forgot to remove the range from the
listfillrange
property.

However, when I select a year it returns that year minus 100 (i.e. 2005 is
selected it returns 1905)

"Tom Ogilvy" wrote:

yr = Year(date)
for i = 0 to 12
Combobox1.AddItem yr + i
Next

--
Regards,
Tom Ogilvy

"JNW" wrote in message
...
The subject doesn't do this justice. I have a combo box for the user
to
select a year from the current year to 12 in the future. The
ListFillRange
for the combo box refers to a sheet with the following formulas:

AD
1 Year
2 =TODAY() - All dates are formatted as YYYY
3 =DATE(YEAR(AD2)+1,MONTH(AD2),DAY(AD2))
4 =DATE(YEAR(AD3)+1,MONTH(AD3),DAY(AD3))
5 ....etc....

Everything works great...you push the button at the side of the combo
box
and it drops down and shows a list of years starting from the current
year.
No problems until a year is selected. i.e. if 2006 is selected it
returns
38991. I know this has to do with the formulas I am using in the
spreadsheet. But I don't know how to fix it.

I used the formulas as a way to not have to update the program every
year.
I want the list to start at 2006 when we hit 1/1/2006.

Thank you for the help.

date in combo formatted as number
 

Sorry I dropped a year. change to:
For i = 0 To 12

Mike F
"Mike Fogleman" wrote in message
...
You need to Dim your variables correctly with dates. Try this. I used it
in the worksheet activate event, but you can put it in a regular code
module.

Option Explicit

Private Sub Worksheet_Activate()
Dim yr As String
Dim i As Long
yr = Year(Date)
ComboBox1.Clear 'clears out old dates and linked cell
For i = 0 To 11
ComboBox1.AddItem yr + i
Next
End Sub

Mike F
"JNW" wrote in message
...
The error was my fault. I forgot to remove the range from the
listfillrange
property.

However, when I select a year it returns that year minus 100 (i.e. 2005
is
selected it returns 1905)

"Tom Ogilvy" wrote:

yr = Year(date)
for i = 0 to 12
Combobox1.AddItem yr + i
Next

--
Regards,
Tom Ogilvy

"JNW" wrote in message
...
The subject doesn't do this justice. I have a combo box for the user
to
select a year from the current year to 12 in the future. The
ListFillRange
for the combo box refers to a sheet with the following formulas:

AD
1 Year
2 =TODAY() - All dates are formatted as YYYY
3 =DATE(YEAR(AD2)+1,MONTH(AD2),DAY(AD2))
4 =DATE(YEAR(AD3)+1,MONTH(AD3),DAY(AD3))
5 ....etc....

Everything works great...you push the button at the side of the combo
box
and it drops down and shows a list of years starting from the current
year.
No problems until a year is selected. i.e. if 2006 is selected it
returns
38991. I know this has to do with the formulas I am using in the
spreadsheet. But I don't know how to fix it.

I used the formulas as a way to not have to update the program every
year.
I want the list to start at 2006 when we hit 1/1/2006.

Thank you for the help.

date in combo formatted as number
 

I'm using xl97 and

? year(date)
2005

so I don't know how you are getting 2005 or what you mean by returns.

Are you trying to convert 2005 to a date?

? cdate(2005)
6/27/1905

dates are stored as the number of days from a base date. In Excel for
windows and VBA, this base date is essentially the beginning of the year
1900.

If you want to convert 2005 to a date you would need

? dateserial(2005,1,1)
1/1/05
? clng(dateserial(2005,1,1))
38353

so Jan 1, 2005 is 38,353 days after the based date.

--
Regards,
Tom Ogilvy



"JNW" wrote in message
...
The error was my fault. I forgot to remove the range from the
listfillrange
property.

However, when I select a year it returns that year minus 100 (i.e. 2005 is
selected it returns 1905)

"Tom Ogilvy" wrote:

yr = Year(date)
for i = 0 to 12
Combobox1.AddItem yr + i
Next

--
Regards,
Tom Ogilvy

"JNW" wrote in message
...
The subject doesn't do this justice. I have a combo box for the user
to
select a year from the current year to 12 in the future. The
ListFillRange
for the combo box refers to a sheet with the following formulas:

AD
1 Year
2 =TODAY() - All dates are formatted as YYYY
3 =DATE(YEAR(AD2)+1,MONTH(AD2),DAY(AD2))
4 =DATE(YEAR(AD3)+1,MONTH(AD3),DAY(AD3))
5 ....etc....

Everything works great...you push the button at the side of the combo
box
and it drops down and shows a list of years starting from the current
year.
No problems until a year is selected. i.e. if 2006 is selected it
returns
38991. I know this has to do with the formulas I am using in the
spreadsheet. But I don't know how to fix it.

I used the formulas as a way to not have to update the program every
year.
I want the list to start at 2006 when we hit 1/1/2006.

Thank you for the help.

date in combo formatted as number
 

Tom, I got what he got using XL2000. Dim yr As String, will work.

Mike F
"Tom Ogilvy" wrote in message
...
I'm using xl97 and

? year(date)
2005

so I don't know how you are getting 2005 or what you mean by returns.

Are you trying to convert 2005 to a date?

? cdate(2005)
6/27/1905

dates are stored as the number of days from a base date. In Excel for
windows and VBA, this base date is essentially the beginning of the year
1900.

If you want to convert 2005 to a date you would need

? dateserial(2005,1,1)
1/1/05
? clng(dateserial(2005,1,1))
38353

so Jan 1, 2005 is 38,353 days after the based date.

--
Regards,
Tom Ogilvy



"JNW" wrote in message
...
The error was my fault. I forgot to remove the range from the
listfillrange
property.

However, when I select a year it returns that year minus 100 (i.e. 2005
is
selected it returns 1905)

"Tom Ogilvy" wrote:

yr = Year(date)
for i = 0 to 12
Combobox1.AddItem yr + i
Next

--
Regards,
Tom Ogilvy

"JNW" wrote in message
...
The subject doesn't do this justice. I have a combo box for the user
to
select a year from the current year to 12 in the future. The
ListFillRange
for the combo box refers to a sheet with the following formulas:

AD
1 Year
2 =TODAY() - All dates are formatted as YYYY
3 =DATE(YEAR(AD2)+1,MONTH(AD2),DAY(AD2))
4 =DATE(YEAR(AD3)+1,MONTH(AD3),DAY(AD3))
5 ....etc....

Everything works great...you push the button at the side of the combo
box
and it drops down and shows a list of years starting from the current
year.
No problems until a year is selected. i.e. if 2006 is selected it
returns
38991. I know this has to do with the formulas I am using in the
spreadsheet. But I don't know how to fix it.

I used the formulas as a way to not have to update the program every
year.
I want the list to start at 2006 when we hit 1/1/2006.

Thank you for the help.

date in combo formatted as number
 

Not formatting the number 2005 as a Date will also work.

--
Regards,
Tom Ogilvy

"Mike Fogleman" wrote in message
...
Tom, I got what he got using XL2000. Dim yr As String, will work.

Mike F
"Tom Ogilvy" wrote in message
...
I'm using xl97 and

? year(date)
2005

so I don't know how you are getting 2005 or what you mean by returns.

Are you trying to convert 2005 to a date?

? cdate(2005)
6/27/1905

dates are stored as the number of days from a base date. In Excel for
windows and VBA, this base date is essentially the beginning of the year
1900.

If you want to convert 2005 to a date you would need

? dateserial(2005,1,1)
1/1/05
? clng(dateserial(2005,1,1))
38353

so Jan 1, 2005 is 38,353 days after the based date.

--
Regards,
Tom Ogilvy



"JNW" wrote in message
...
The error was my fault. I forgot to remove the range from the
listfillrange
property.

However, when I select a year it returns that year minus 100 (i.e. 2005
is
selected it returns 1905)

"Tom Ogilvy" wrote:

yr = Year(date)
for i = 0 to 12
Combobox1.AddItem yr + i
Next

--
Regards,
Tom Ogilvy

"JNW" wrote in message
...
The subject doesn't do this justice. I have a combo box for the
user
to
select a year from the current year to 12 in the future. The
ListFillRange
for the combo box refers to a sheet with the following formulas:

AD
1 Year
2 =TODAY() - All dates are formatted as YYYY
3 =DATE(YEAR(AD2)+1,MONTH(AD2),DAY(AD2))
4 =DATE(YEAR(AD3)+1,MONTH(AD3),DAY(AD3))
5 ....etc....

Everything works great...you push the button at the side of the
combo
box
and it drops down and shows a list of years starting from the
current
year.
No problems until a year is selected. i.e. if 2006 is selected it
returns
38991. I know this has to do with the formulas I am using in the
spreadsheet. But I don't know how to fix it.

I used the formulas as a way to not have to update the program
every
year.
I want the list to start at 2006 when we hit 1/1/2006.

Thank you for the help.