|
Scroll to Current Month on Combo box using Data Validation
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, |
Scroll to Current Month on Combo box using Data Validation
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, |
Scroll to Current Month on Combo box using Data Validation
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, |
Scroll to Current Month on Combo box using Data Validation
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, |
Scroll to Current Month on Combo box using Data Validation
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, |
Scroll to Current Month on Combo box using Data Validation
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, |
Scroll to Current Month on Combo box using Data Validation
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, |
Scroll to Current Month on Combo box using Data Validation
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, |
Scroll to Current Month on Combo box using Data Validation
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 |
| All times are GMT +1. The time now is 03:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com