Default Value in a list box

[Secret Squirrel]

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?

 

[Roger Govier]

Hi

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

 

[Secret Squirrel]

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?

 

[Dave Peterson]

You can use something like:

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

 

[Secret Squirrel]

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]

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

Option Explicit
Sub Autpen()
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]

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]

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.

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

 

[Secret Squirrel]

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