Using Data Validation I have a combo box with a list =payperiods (which are
1/9/04
1/23/04
2/6/04
2/20/04
3/5/04...
5/28/04
6/11/04...
12/24/04

Here in the May/June time of the year, I'd like to have when the employee
clicks
on the DownArrow of the Combo box instead of seeing the first 8 entries
being 1/9/04, 1/23/04 etc I'd like to based on =now() or today() have the
listing "start-with" the first date meeting my =now() criteria. Can this be
done?

Thanks in advance,

I don't believe you have any control of the list box created by a
Data Validation list.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"JMay" wrote in message
news:Zfmuc.2922$Tw.153@lakeread06...
Using Data Validation I have a combo box with a list
=payperiods (which are
1/9/04
1/23/04
2/6/04
2/20/04
3/5/04...
5/28/04
6/11/04...
12/24/04

Here in the May/June time of the year, I'd like to have when
the employee
clicks
on the DownArrow of the Combo box instead of seeing the first 8
entries
being 1/9/04, 1/23/04 etc I'd like to based on =now() or
today() have the
listing "start-with" the first date meeting my =now() criteria.
Can this be
done?

Thanks in advance,

You could set the value of the DV cell to Today, this will force it.

Interestingly, you couldn't do that from the worksheet if that date is not
in the list, but you can from VBA.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Chip Pearson" wrote in message
...
I don't believe you have any control of the list box created by a
Data Validation list.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"JMay" wrote in message
news:Zfmuc.2922$Tw.153@lakeread06...
Using Data Validation I have a combo box with a list
=payperiods (which are
1/9/04
1/23/04
2/6/04
2/20/04
3/5/04...
5/28/04
6/11/04...
12/24/04

Here in the May/June time of the year, I'd like to have when
the employee
clicks
on the DownArrow of the Combo box instead of seeing the first 8
entries
being 1/9/04, 1/23/04 etc I'd like to based on =now() or
today() have the
listing "start-with" the first date meeting my =now() criteria.
Can this be
done?

Thanks in advance,

How could I accomplish it in VBA?
If done in VBA would it cause abandonment of my Data Validation set up?
Should I switch to using the Control toolbox options and
do this working with the design view << have only been in this feature
twice?
Tks Bob,

"Bob Phillips" wrote in message
...
You could set the value of the DV cell to Today, this will force it.

Interestingly, you couldn't do that from the worksheet if that date is not
in the list, but you can from VBA.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Chip Pearson" wrote in message
...
I don't believe you have any control of the list box created by a
Data Validation list.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"JMay" wrote in message
news:Zfmuc.2922$Tw.153@lakeread06...
Using Data Validation I have a combo box with a list
=payperiods (which are
1/9/04
1/23/04
2/6/04
2/20/04
3/5/04...
5/28/04
6/11/04...
12/24/04

Here in the May/June time of the year, I'd like to have when
the employee
clicks
on the DownArrow of the Combo box instead of seeing the first 8
entries
being 1/9/04, 1/23/04 etc I'd like to based on =now() or
today() have the
listing "start-with" the first date meeting my =now() criteria.
Can this be
done?

Thanks in advance,

No, I did it with the following simple instruction

range("G1")=date

where G1 is where the DV cell is located.

If you have only used control combos twice before, I would suggest you try
them some more. By playing with it, you'll know what the pros and cons are,
and be better equipped to making an informed decision next problem.
Personally, and I think I am not in the mainstream here , I never use Forms
controls, and rarely use DV, but use control toolbox a lot.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"JMay" wrote in message news:CKmuc.2938$Tw.2828@lakeread06...
How could I accomplish it in VBA?
If done in VBA would it cause abandonment of my Data Validation set up?
Should I switch to using the Control toolbox options and
do this working with the design view << have only been in this feature
twice?
Tks Bob,

"Bob Phillips" wrote in message
...
You could set the value of the DV cell to Today, this will force it.

Interestingly, you couldn't do that from the worksheet if that date is
not
in the list, but you can from VBA.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Chip Pearson" wrote in message
...
I don't believe you have any control of the list box created by a
Data Validation list.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"JMay" wrote in message
news:Zfmuc.2922$Tw.153@lakeread06...
Using Data Validation I have a combo box with a list
=payperiods (which are
1/9/04
1/23/04
2/6/04
2/20/04
3/5/04...
5/28/04
6/11/04...
12/24/04

Here in the May/June time of the year, I'd like to have when
the employee
clicks
on the DownArrow of the Combo box instead of seeing the first 8
entries
being 1/9/04, 1/23/04 etc I'd like to based on =now() or
today() have the
listing "start-with" the first date meeting my =now() criteria.
Can this be
done?

Thanks in advance,

Tks Bob:
My Dates in my list are predefined and unchangeable. My entry must be one or
none of the 26 Fridays in the year 2004. I was just wanting "cosmetically"
to allow user once they clicked on the drop-down arrow that it would reflect
a more current date (say out here in May or June) and not begin with January
9 ((having to scroll down then to June).

Using Open VBA I could probably do an If statement referring to the list and
set the focus to the first date (in the list) that is "=<" date... but
doing this is currently over-my-head -- but I'm gonna try and give it a
shot. Any hints, am i trying something that is not possible?


"Bob Phillips" wrote in message
...
No, I did it with the following simple instruction

range("G1")=date

where G1 is where the DV cell is located.

If you have only used control combos twice before, I would suggest you try
them some more. By playing with it, you'll know what the pros and cons
are,
and be better equipped to making an informed decision next problem.
Personally, and I think I am not in the mainstream here , I never use
Forms
controls, and rarely use DV, but use control toolbox a lot.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"JMay" wrote in message
news:CKmuc.2938$Tw.2828@lakeread06...
How could I accomplish it in VBA?
If done in VBA would it cause abandonment of my Data Validation set up?
Should I switch to using the Control toolbox options and
do this working with the design view << have only been in this feature
twice?
Tks Bob,

"Bob Phillips" wrote in message
...
You could set the value of the DV cell to Today, this will force it.

Interestingly, you couldn't do that from the worksheet if that date is
not
in the list, but you can from VBA.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Chip Pearson" wrote in message
...
I don't believe you have any control of the list box created by a
Data Validation list.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"JMay" wrote in message
news:Zfmuc.2922$Tw.153@lakeread06...
Using Data Validation I have a combo box with a list
=payperiods (which are
1/9/04
1/23/04
2/6/04
2/20/04
3/5/04...
5/28/04
6/11/04...
12/24/04

Here in the May/June time of the year, I'd like to have when
the employee
clicks
on the DownArrow of the Combo box instead of seeing the first 8
entries
being 1/9/04, 1/23/04 etc I'd like to based on =now() or
today() have the
listing "start-with" the first date meeting my =now() criteria.
Can this be
done?

Thanks in advance,

Hi JMay,

Not difficult really, but it all depends on where the data is located.

In essence, you need to determine an index into the range of dates. You can
determine that index using Date

myDate = Date

and then determine the Friday before. This would be done with weekday. As
Friday is weekday 6 in the default setting, so it is useful to shunt the day
on 1 by adding 1 as if you were working with Saturday, as this makes some
calculations simpler. Subtract this weekday number from the today date and
you get the previous Friday. Only small glitch here is that if today is
Friday, it still gives the previous Friday, so if you want to avoid this (it
may suit) test for it and don't do the subtract. Remember to test for 7 as
the weekday calc is on the day +1.

Once you have a date that is the previous Friday, you can just load that
into the DV cell as I showed before

Range("G1").Value = myFridayDate

Give it a go, if you get stuck, just post back and I will give you the code.

Regards

Bob

"JMay" wrote in message news:5ynuc.3037$Tw.1475@lakeread06...
Tks Bob:
My Dates in my list are predefined and unchangeable. My entry must be one
or
none of the 26 Fridays in the year 2004. I was just wanting
"cosmetically"
to allow user once they clicked on the drop-down arrow that it would
reflect
a more current date (say out here in May or June) and not begin with
January
9 ((having to scroll down then to June).

Using Open VBA I could probably do an If statement referring to the list
and
set the focus to the first date (in the list) that is "=<" date... but
doing this is currently over-my-head -- but I'm gonna try and give it a
shot. Any hints, am i trying something that is not possible?


"Bob Phillips" wrote in message
...
No, I did it with the following simple instruction

range("G1")=date

where G1 is where the DV cell is located.

If you have only used control combos twice before, I would suggest you
try
them some more. By playing with it, you'll know what the pros and cons
are,
and be better equipped to making an informed decision next problem.
Personally, and I think I am not in the mainstream here , I never use
Forms
controls, and rarely use DV, but use control toolbox a lot.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"JMay" wrote in message
news:CKmuc.2938$Tw.2828@lakeread06...
How could I accomplish it in VBA?
If done in VBA would it cause abandonment of my Data Validation set
up?
Should I switch to using the Control toolbox options and
do this working with the design view << have only been in this feature
twice?
Tks Bob,

"Bob Phillips" wrote in message
...
You could set the value of the DV cell to Today, this will force it.

Interestingly, you couldn't do that from the worksheet if that date
is
not
in the list, but you can from VBA.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Chip Pearson" wrote in message
...
I don't believe you have any control of the list box created by a
Data Validation list.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"JMay" wrote in message
news:Zfmuc.2922$Tw.153@lakeread06...
Using Data Validation I have a combo box with a list
=payperiods (which are
1/9/04
1/23/04
2/6/04
2/20/04
3/5/04...
5/28/04
6/11/04...
12/24/04

Here in the May/June time of the year, I'd like to have when
the employee
clicks
on the DownArrow of the Combo box instead of seeing the first 8
entries
being 1/9/04, 1/23/04 etc I'd like to based on =now() or
today() have the
listing "start-with" the first date meeting my =now() criteria.
Can this be
done?

Thanks in advance,

wow!!!!!
Bob Thanks, before I even start on this I'm gonna print-it-off, get a big
cup of coffee,
sit down, with pen and paper,, wow Thanks IN ADVANCE for this input.
Thanks for flattening out the learning curve (not supplied by books, I own 6
excel books).
Bless you,
JMay



"Bob Phillips" wrote in message
...
Hi JMay,

Not difficult really, but it all depends on where the data is located.

In essence, you need to determine an index into the range of dates. You
can
determine that index using Date

myDate = Date

and then determine the Friday before. This would be done with weekday. As
Friday is weekday 6 in the default setting, so it is useful to shunt the
day
on 1 by adding 1 as if you were working with Saturday, as this makes some
calculations simpler. Subtract this weekday number from the today date
and
you get the previous Friday. Only small glitch here is that if today is
Friday, it still gives the previous Friday, so if you want to avoid this
(it
may suit) test for it and don't do the subtract. Remember to test for 7 as
the weekday calc is on the day +1.

Once you have a date that is the previous Friday, you can just load that
into the DV cell as I showed before

Range("G1").Value = myFridayDate

Give it a go, if you get stuck, just post back and I will give you the
code.

Regards

Bob

"JMay" wrote in message
news:5ynuc.3037$Tw.1475@lakeread06...
Tks Bob:
My Dates in my list are predefined and unchangeable. My entry must be
one
or
none of the 26 Fridays in the year 2004. I was just wanting
"cosmetically"
to allow user once they clicked on the drop-down arrow that it would
reflect
a more current date (say out here in May or June) and not begin with
January
9 ((having to scroll down then to June).

Using Open VBA I could probably do an If statement referring to the list
and
set the focus to the first date (in the list) that is "=<" date... but
doing this is currently over-my-head -- but I'm gonna try and give it a
shot. Any hints, am i trying something that is not possible?


"Bob Phillips" wrote in message
...
No, I did it with the following simple instruction

range("G1")=date

where G1 is where the DV cell is located.

If you have only used control combos twice before, I would suggest you
try
them some more. By playing with it, you'll know what the pros and cons
are,
and be better equipped to making an informed decision next problem.
Personally, and I think I am not in the mainstream here , I never use
Forms
controls, and rarely use DV, but use control toolbox a lot.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"JMay" wrote in message
news:CKmuc.2938$Tw.2828@lakeread06...
How could I accomplish it in VBA?
If done in VBA would it cause abandonment of my Data Validation set
up?
Should I switch to using the Control toolbox options and
do this working with the design view << have only been in this
feature
twice?
Tks Bob,

"Bob Phillips" wrote in message
...
You could set the value of the DV cell to Today, this will force
it.

Interestingly, you couldn't do that from the worksheet if that
date
is
not
in the list, but you can from VBA.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Chip Pearson" wrote in message
...
I don't believe you have any control of the list box created by
a
Data Validation list.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"JMay" wrote in message
news:Zfmuc.2922$Tw.153@lakeread06...
Using Data Validation I have a combo box with a list
=payperiods (which are
1/9/04
1/23/04
2/6/04
2/20/04
3/5/04...
5/28/04
6/11/04...
12/24/04

Here in the May/June time of the year, I'd like to have when
the employee
clicks
on the DownArrow of the Combo box instead of seeing the first
8
entries
being 1/9/04, 1/23/04 etc I'd like to based on =now() or
today() have the
listing "start-with" the first date meeting my =now()
criteria.
Can this be
done?

Thanks in advance,

I've just counted, I have 12 VB books<vbg.

And I have to buy another as I have just got hold of VS.Net.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"JMay" wrote in message news:S0ouc.3135$Tw.1365@lakeread06...
wow!!!!!
Bob Thanks, before I even start on this I'm gonna print-it-off, get a big
cup of coffee,
sit down, with pen and paper,, wow Thanks IN ADVANCE for this input.
Thanks for flattening out the learning curve (not supplied by books, I own
6
excel books).
Bless you,
JMay