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

No typos. I tried it but nothing. I put the code in the VBA window behind
that sheet, correct? Am I missing something else?

"Dave Peterson" wrote:

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

I assumed that you wanted to populate that cell when you opened the workbook.
Is that ok?

If it is ok, then this code goes in a general module.

If it's not ok, when should that cell be populated?

And remember to change the name of the worksheet and the address of the cell,
too.

Secret Squirrel wrote:

No typos. I tried it but nothing. I put the code in the VBA window behind
that sheet, correct? Am I missing something else?

"Dave Peterson" wrote:

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


--

Dave Peterson

I didn't realize I had to put it into a general module. It works now.
Thanks for your help Dave!

"Dave Peterson" wrote:

I assumed that you wanted to populate that cell when you opened the workbook.
Is that ok?

If it is ok, then this code goes in a general module.

If it's not ok, when should that cell be populated?

And remember to change the name of the worksheet and the address of the cell,
too.

Secret Squirrel wrote:

No typos. I tried it but nothing. I put the code in the VBA window behind
that sheet, correct? Am I missing something else?

"Dave Peterson" wrote:

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


--

Dave Peterson