I have a list box in cell A8. It's a list box created using data validation.
The values are all dates. How do I get it to populate a default date when
opening the file?
The list of values is dates for every Monday so I want it to default to the
current week's Monday. For example if today was 9/12/07 I would want it to be
9/10/07 when the file is opened. How would I make this happen?

Hi

Try
=TODAY()-WEEKDAY(TODAY())+2

--
Regards
Roger Govier



"Secret Squirrel" wrote in
message ...
I have a list box in cell A8. It's a list box created using data
validation.
The values are all dates. How do I get it to populate a default date when
opening the file?
The list of values is dates for every Monday so I want it to default to
the
current week's Monday. For example if today was 9/12/07 I would want it to
be
9/10/07 when the file is opened. How would I make this happen?

Thanks. That should work but how do I put that into VBA code? I think that's
where it needs to go to populate the default of the list box. Correct?

"Roger Govier" wrote:

Hi

Try
=TODAY()-WEEKDAY(TODAY())+2

--
Regards
Roger Govier



"Secret Squirrel" wrote in
message ...
I have a list box in cell A8. It's a list box created using data
validation.
The values are all dates. How do I get it to populate a default date when
opening the file?
The list of values is dates for every Monday so I want it to default to
the
current week's Monday. For example if today was 9/12/07 I would want it to
be
9/10/07 when the file is opened. How would I make this happen?

You can use something like:

Dim myDate As Date
myDate = Date - Weekday(Date) + 2
MsgBox myDate



Secret Squirrel wrote:

Thanks. That should work but how do I put that into VBA code? I think that's
where it needs to go to populate the default of the list box. Correct?

"Roger Govier" wrote:

Hi

Try
=TODAY()-WEEKDAY(TODAY())+2

--
Regards
Roger Govier



"Secret Squirrel" wrote in
message ...
I have a list box in cell A8. It's a list box created using data
validation.
The values are all dates. How do I get it to populate a default date when
opening the file?
The list of values is dates for every Monday so I want it to default to
the
current week's Monday. For example if today was 9/12/07 I would want it to
be
9/10/07 when the file is opened. How would I make this happen?




--

Dave Peterson

Hi Dave,
Thanks for the response.
How would I point that code to my list box on my worksheet so it will set
that as the default date when the file is opened?

"Dave Peterson" wrote:

You can use something like:

Dim myDate As Date
myDate = Date - Weekday(Date) + 2
MsgBox myDate



Secret Squirrel wrote:

Thanks. That should work but how do I put that into VBA code? I think that's
where it needs to go to populate the default of the list box. Correct?

"Roger Govier" wrote:

Hi

Try
=TODAY()-WEEKDAY(TODAY())+2

--
Regards
Roger Govier



"Secret Squirrel" wrote in
message ...
I have a list box in cell A8. It's a list box created using data
validation.
The values are all dates. How do I get it to populate a default date when
opening the file?
The list of values is dates for every Monday so I want it to default to
the
current week's Monday. For example if today was 9/12/07 I would want it to
be
9/10/07 when the file is opened. How would I make this happen?




--

Dave Peterson

If you're using data|validation for that cell, you can just plop that value into
that cell:

Option Explicit
Sub Auto_Open()
Dim myDate As Date
myDate = Date - Weekday(Date) + 2

with thisworkbook.worksheets("Sheet9999").range("x999")
.numberformat = "mmmm dd, yyyy"
.value = mydate
end with

end sub

Untested. Watch for typos!

Secret Squirrel wrote:

Hi Dave,
Thanks for the response.
How would I point that code to my list box on my worksheet so it will set
that as the default date when the file is opened?

"Dave Peterson" wrote:

You can use something like:

Dim myDate As Date
myDate = Date - Weekday(Date) + 2
MsgBox myDate



Secret Squirrel wrote:

Thanks. That should work but how do I put that into VBA code? I think that's
where it needs to go to populate the default of the list box. Correct?

"Roger Govier" wrote:

Hi

Try
=TODAY()-WEEKDAY(TODAY())+2

--
Regards
Roger Govier



"Secret Squirrel" wrote in
message ...
I have a list box in cell A8. It's a list box created using data
validation.
The values are all dates. How do I get it to populate a default date when
opening the file?
The list of values is dates for every Monday so I want it to default to
the
current week's Monday. For example if today was 9/12/07 I would want it to
be
9/10/07 when the file is opened. How would I make this happen?




--

Dave Peterson


--

Dave Peterson