Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to make drop-down list of dates start with current date?
I have a drop-down list (Data/Validation) that contains dates from the
beginning of the fiscal year (10-01-2004) to the end of the fiscal year (09-30-2005). Is there a way to have the drop down start at the current date instead at the beginning on October 01? Thanks. |
#2
|
|||
|
|||
One way:
In a second sheet, enter A1: =TODAY() A2: =A1 + 1 Format A2 as a date, and copy down to A366. Choose Insert/Name/Define and enter Name in workbook: DateList Refers To: =OFFSET(Sheet2!$A$1,0,0,365+(DAY(Sheet2!$A$1)<DAY (Sheet2!$A$366)),1) Then in your validation cell, use Allow: List Source: =DateList In article , "Jan Buckley" wrote: I have a drop-down list (Data/Validation) that contains dates from the beginning of the fiscal year (10-01-2004) to the end of the fiscal year (09-30-2005). Is there a way to have the drop down start at the current date instead at the beginning on October 01? Thanks. |
#3
|
|||
|
|||
JE: I was thrilled when, using your instructions, I got this to work.
However, I now find that ,not only can I not back up in the list to a date prior to TODAY(), neither can I type in a previous date. Since there are times when it will be necessary to enter an earlier date (previous to TODAY()), is there a solution to this? I sure hope so - setting the date to the current date is very convenient and a time saver, but I won't be able to use it if I can't 'back up'. Thanks so much. Jan "JE McGimpsey" wrote: One way: In a second sheet, enter A1: =TODAY() A2: =A1 + 1 Format A2 as a date, and copy down to A366. Choose Insert/Name/Define and enter Name in workbook: DateList Refers To: =OFFSET(Sheet2!$A$1,0,0,365+(DAY(Sheet2!$A$1)<DAY (Sheet2!$A$366)),1) Then in your validation cell, use Allow: List Source: =DateList In article , "Jan Buckley" wrote: I have a drop-down list (Data/Validation) that contains dates from the beginning of the fiscal year (10-01-2004) to the end of the fiscal year (09-30-2005). Is there a way to have the drop down start at the current date instead at the beginning on October 01? Thanks. |
#4
|
|||
|
|||
AFAIK, there's no way to get the validation list to pop up in the middle
of the list. One workaround would be to create a Combobox from the control toolbox on the worksheet that would dynamically load the FY's dates, and preselect today's date: Enter this code behind it (right-click the combobox and choose View Code): Private Sub ComboBox1_Gotfocus() Dim dDates() As Date Dim nFY As Long Dim i As Long nFY = Year(Date + 92) ReDim dDates(0 to DateSerial(nFY, 12, 31) - DateSerial(nFY, 1, 0)) dDates(0) = DateSerial(nFY - 1, 10, 1) For i = 1 To UBound(dDates) dDates(i) = dDates(0) + i Next i With ComboBox1 .List = dDates .ListIndex = Date - dDates(0) End With End Sub Private Sub ComboBox1_Click() Range("A1").Value = ComboBox1.Value End Sub In article , "Jan Buckley" wrote: JE: I was thrilled when, using your instructions, I got this to work. However, I now find that ,not only can I not back up in the list to a date prior to TODAY(), neither can I type in a previous date. Since there are times when it will be necessary to enter an earlier date (previous to TODAY()), is there a solution to this? I sure hope so - setting the date to the current date is very convenient and a time saver, but I won't be able to use it if I can't 'back up'. Thanks so much. Jan |
#5
|
|||
|
|||
JE:
I typed in the code behind the combo box as instructed, but it doesn't work. When I move my cursor over the box, the little four-sided black arrows appear and I can't click into it. Also, does the A1 in the code (Private Sub ComboBox1_click()) refer to the DateList we constructed yesterday? Do I need to keep that list? I'm not a programmer as I'm sure you've been able to devise. . . . Help! "JE McGimpsey" wrote: AFAIK, there's no way to get the validation list to pop up in the middle of the list. One workaround would be to create a Combobox from the control toolbox on the worksheet that would dynamically load the FY's dates, and preselect today's date: Enter this code behind it (right-click the combobox and choose View Code): Private Sub ComboBox1_Gotfocus() Dim dDates() As Date Dim nFY As Long Dim i As Long nFY = Year(Date + 92) ReDim dDates(0 to DateSerial(nFY, 12, 31) - DateSerial(nFY, 1, 0)) dDates(0) = DateSerial(nFY - 1, 10, 1) For i = 1 To UBound(dDates) dDates(i) = dDates(0) + i Next i With ComboBox1 .List = dDates .ListIndex = Date - dDates(0) End With End Sub Private Sub ComboBox1_Click() Range("A1").Value = ComboBox1.Value End Sub In article , "Jan Buckley" wrote: JE: I was thrilled when, using your instructions, I got this to work. However, I now find that ,not only can I not back up in the list to a date prior to TODAY(), neither can I type in a previous date. Since there are times when it will be necessary to enter an earlier date (previous to TODAY()), is there a solution to this? I sure hope so - setting the date to the current date is very convenient and a time saver, but I won't be able to use it if I can't 'back up'. Thanks so much. Jan |
#6
|
|||
|
|||
You need to exit Design Mode (by default, the first control on the
Control Toolbox toolbar). No, with the code I gave you, there's no need to use Date_List any longer. The "A1" referred to the cell you want the date to appear in. In article , "Jan Buckley" wrote: I typed in the code behind the combo box as instructed, but it doesn't work. When I move my cursor over the box, the little four-sided black arrows appear and I can't click into it. Also, does the A1 in the code (Private Sub ComboBox1_click()) refer to the DateList we constructed yesterday? Do I need to keep that list? I'm not a programmer as I'm sure you've been able to devise. . . . Help! |
#7
|
|||
|
|||
JE: Works like a charm. You guys do GOOD WORK! Thanks so much.
jan "JE McGimpsey" wrote: You need to exit Design Mode (by default, the first control on the Control Toolbox toolbar). No, with the code I gave you, there's no need to use Date_List any longer. The "A1" referred to the cell you want the date to appear in. In article , "Jan Buckley" wrote: I typed in the code behind the combo box as instructed, but it doesn't work. When I move my cursor over the box, the little four-sided black arrows appear and I can't click into it. Also, does the A1 in the code (Private Sub ComboBox1_click()) refer to the DateList we constructed yesterday? Do I need to keep that list? I'm not a programmer as I'm sure you've been able to devise. . . . Help! |
#8
|
|||
|
|||
JE: I hate to bother you again, but is there a way to "hide" the combo box
until you click or tab into the cell where it resides? I have four columns (containing up to 500 rows each) on my spreadsheet that will require the combo boxes and it looks 'ugly' with them all showing. Thanks. "JE McGimpsey" wrote: You need to exit Design Mode (by default, the first control on the Control Toolbox toolbar). No, with the code I gave you, there's no need to use Date_List any longer. The "A1" referred to the cell you want the date to appear in. In article , "Jan Buckley" wrote: I typed in the code behind the combo box as instructed, but it doesn't work. When I move my cursor over the box, the little four-sided black arrows appear and I can't click into it. Also, does the A1 in the code (Private Sub ComboBox1_click()) refer to the DateList we constructed yesterday? Do I need to keep that list? I'm not a programmer as I'm sure you've been able to devise. . . . Help! |
#9
|
|||
|
|||
One way:
For 1 combobox, add this to your worksheet code module (adjust "A1" to suit): Private Sub Worksheet_SelectionChange( _ ByVal Target As Range) ComboBox1.Visible = _ Not (Intersect(Target, Range("A1")) Is Nothing) End Sub However, if you've got 500 rows that require comboboxes, I'd consider rethinking the approach. Perhaps using a single combobox and positioning it at the appropriate cell whenever one of the cells is selected, then placing the value of the combobox into the selected cell rather than hardcoding it. In article , "Jan Buckley" wrote: I hate to bother you again, but is there a way to "hide" the combo box until you click or tab into the cell where it resides? I have four columns (containing up to 500 rows each) on my spreadsheet that will require the combo boxes and it looks 'ugly' with them all showing. Thanks. |
#10
|
|||
|
|||
JE: That sounds like it would work much better but, not being a programmer, I
don't know how to accomplish it. Can you help? "JE McGimpsey" wrote: One way: For 1 combobox, add this to your worksheet code module (adjust "A1" to suit): Private Sub Worksheet_SelectionChange( _ ByVal Target As Range) ComboBox1.Visible = _ Not (Intersect(Target, Range("A1")) Is Nothing) End Sub However, if you've got 500 rows that require comboboxes, I'd consider rethinking the approach. Perhaps using a single combobox and positioning it at the appropriate cell whenever one of the cells is selected, then placing the value of the combobox into the selected cell rather than hardcoding it. In article , "Jan Buckley" wrote: I hate to bother you again, but is there a way to "hide" the combo box until you click or tab into the cell where it resides? I have four columns (containing up to 500 rows each) on my spreadsheet that will require the combo boxes and it looks 'ugly' with them all showing. Thanks. |
#11
|
|||
|
|||
I put a combobox from the control toolbox toolbar on the worksheet.
I put all this code behind the worksheet: Option Explicit Private Sub ComboBox1_Change() ActiveCell.Value = Me.ComboBox1.Value End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) With Me.ComboBox1 If Target.Cells.Count 1 Then .Visible = False Else .Visible _ = Not CBool(Intersect(Me.Range("a1:a30"), Target) Is Nothing) If .Visible = True Then .Top = Target.Top .Left = Target.Left + Target.Width End If End If End With End Sub Private Sub ComboBox1_Gotfocus() Dim dDates() As Date Dim nFY As Long Dim i As Long nFY = Year(Date + 92) ReDim dDates(0 To DateSerial(nFY, 12, 31) - DateSerial(nFY, 1, 0)) dDates(0) = DateSerial(nFY - 1, 10, 1) For i = 1 To UBound(dDates) dDates(i) = dDates(0) + i Next i With ComboBox1 .List = dDates .ListIndex = Date - dDates(0) End With End Sub ======== I only checked to see if I was in A1:A30. Change that to what you want. Jan Buckley wrote: JE: That sounds like it would work much better but, not being a programmer, I don't know how to accomplish it. Can you help? "JE McGimpsey" wrote: One way: For 1 combobox, add this to your worksheet code module (adjust "A1" to suit): Private Sub Worksheet_SelectionChange( _ ByVal Target As Range) ComboBox1.Visible = _ Not (Intersect(Target, Range("A1")) Is Nothing) End Sub However, if you've got 500 rows that require comboboxes, I'd consider rethinking the approach. Perhaps using a single combobox and positioning it at the appropriate cell whenever one of the cells is selected, then placing the value of the combobox into the selected cell rather than hardcoding it. In article , "Jan Buckley" wrote: I hate to bother you again, but is there a way to "hide" the combo box until you click or tab into the cell where it resides? I have four columns (containing up to 500 rows each) on my spreadsheet that will require the combo boxes and it looks 'ugly' with them all showing. Thanks. -- Dave Peterson |
#12
|
|||
|
|||
Dave, this works great, can't tell you how much I've learned during this
little exercise. But is there any way that the user can TAB to the next cell (after selecting the date from the combo box) rather than having to CLICK into it? Thanks. "Jan Buckley" wrote: JE: That sounds like it would work much better but, not being a programmer, I don't know how to accomplish it. Can you help? "JE McGimpsey" wrote: One way: For 1 combobox, add this to your worksheet code module (adjust "A1" to suit): Private Sub Worksheet_SelectionChange( _ ByVal Target As Range) ComboBox1.Visible = _ Not (Intersect(Target, Range("A1")) Is Nothing) End Sub However, if you've got 500 rows that require comboboxes, I'd consider rethinking the approach. Perhaps using a single combobox and positioning it at the appropriate cell whenever one of the cells is selected, then placing the value of the combobox into the selected cell rather than hardcoding it. In article , "Jan Buckley" wrote: I hate to bother you again, but is there a way to "hide" the combo box until you click or tab into the cell where it resides? I have four columns (containing up to 500 rows each) on my spreadsheet that will require the combo boxes and it looks 'ugly' with them all showing. Thanks. |
#13
|
|||
|
|||
If the next cell is down one:
Private Sub ComboBox1_Change() ActiveCell.Value = Me.ComboBox1.Value ActiveCell.Offset(1, 0).Activate End Sub If to the right one: Private Sub ComboBox1_Change() ActiveCell.Value = Me.ComboBox1.Value ActiveCell.Offset(0, 1).Activate End Sub Maybe????? Jan Buckley wrote: Dave, this works great, can't tell you how much I've learned during this little exercise. But is there any way that the user can TAB to the next cell (after selecting the date from the combo box) rather than having to CLICK into it? Thanks. "Jan Buckley" wrote: JE: That sounds like it would work much better but, not being a programmer, I don't know how to accomplish it. Can you help? "JE McGimpsey" wrote: One way: For 1 combobox, add this to your worksheet code module (adjust "A1" to suit): Private Sub Worksheet_SelectionChange( _ ByVal Target As Range) ComboBox1.Visible = _ Not (Intersect(Target, Range("A1")) Is Nothing) End Sub However, if you've got 500 rows that require comboboxes, I'd consider rethinking the approach. Perhaps using a single combobox and positioning it at the appropriate cell whenever one of the cells is selected, then placing the value of the combobox into the selected cell rather than hardcoding it. In article , "Jan Buckley" wrote: I hate to bother you again, but is there a way to "hide" the combo box until you click or tab into the cell where it resides? I have four columns (containing up to 500 rows each) on my spreadsheet that will require the combo boxes and it looks 'ugly' with them all showing. Thanks. -- Dave Peterson |
#14
|
|||
|
|||
The "next cell" will always be one to the right. Can you write the code for
that? You guys sure are patient with us beginners, and I really appreciate it. "Dave Peterson" wrote: If the next cell is down one: Private Sub ComboBox1_Change() ActiveCell.Value = Me.ComboBox1.Value ActiveCell.Offset(1, 0).Activate End Sub If to the right one: Private Sub ComboBox1_Change() ActiveCell.Value = Me.ComboBox1.Value ActiveCell.Offset(0, 1).Activate End Sub Maybe????? Jan Buckley wrote: Dave, this works great, can't tell you how much I've learned during this little exercise. But is there any way that the user can TAB to the next cell (after selecting the date from the combo box) rather than having to CLICK into it? Thanks. "Jan Buckley" wrote: JE: That sounds like it would work much better but, not being a programmer, I don't know how to accomplish it. Can you help? "JE McGimpsey" wrote: One way: For 1 combobox, add this to your worksheet code module (adjust "A1" to suit): Private Sub Worksheet_SelectionChange( _ ByVal Target As Range) ComboBox1.Visible = _ Not (Intersect(Target, Range("A1")) Is Nothing) End Sub However, if you've got 500 rows that require comboboxes, I'd consider rethinking the approach. Perhaps using a single combobox and positioning it at the appropriate cell whenever one of the cells is selected, then placing the value of the combobox into the selected cell rather than hardcoding it. In article , "Jan Buckley" wrote: I hate to bother you again, but is there a way to "hide" the combo box until you click or tab into the cell where it resides? I have four columns (containing up to 500 rows each) on my spreadsheet that will require the combo boxes and it looks 'ugly' with them all showing. Thanks. -- Dave Peterson |
#15
|
|||
|
|||
That was the second one:
If to the right one: Private Sub ComboBox1_Change() ActiveCell.Value = Me.ComboBox1.Value ActiveCell.Offset(0, 1).Activate End Sub But I gotta believe that your columns are limited--say B:Z. When you enter data in B3, go to C3, ..., but when you get to Z3, go to B4???? If that's ok, you could change that sub: If to the right one: Private Sub ComboBox1_Change() ActiveCell.Value = Me.ComboBox1.Value if activecell.column = range("z1").column then cells(activcell.row+1,"B").activate else ActiveCell.Offset(0, 1).Activate end if End Sub Change z1 to the correct column (the row won't matter). And change "B" to the first column in the range. Jan Buckley wrote: The "next cell" will always be one to the right. Can you write the code for that? You guys sure are patient with us beginners, and I really appreciate it. "Dave Peterson" wrote: If the next cell is down one: Private Sub ComboBox1_Change() ActiveCell.Value = Me.ComboBox1.Value ActiveCell.Offset(1, 0).Activate End Sub If to the right one: Private Sub ComboBox1_Change() ActiveCell.Value = Me.ComboBox1.Value ActiveCell.Offset(0, 1).Activate End Sub Maybe????? Jan Buckley wrote: Dave, this works great, can't tell you how much I've learned during this little exercise. But is there any way that the user can TAB to the next cell (after selecting the date from the combo box) rather than having to CLICK into it? Thanks. "Jan Buckley" wrote: JE: That sounds like it would work much better but, not being a programmer, I don't know how to accomplish it. Can you help? "JE McGimpsey" wrote: One way: For 1 combobox, add this to your worksheet code module (adjust "A1" to suit): Private Sub Worksheet_SelectionChange( _ ByVal Target As Range) ComboBox1.Visible = _ Not (Intersect(Target, Range("A1")) Is Nothing) End Sub However, if you've got 500 rows that require comboboxes, I'd consider rethinking the approach. Perhaps using a single combobox and positioning it at the appropriate cell whenever one of the cells is selected, then placing the value of the combobox into the selected cell rather than hardcoding it. In article , "Jan Buckley" wrote: I hate to bother you again, but is there a way to "hide" the combo box until you click or tab into the cell where it resides? I have four columns (containing up to 500 rows each) on my spreadsheet that will require the combo boxes and it looks 'ugly' with them all showing. Thanks. -- Dave Peterson -- Dave Peterson |
#16
|
|||
|
|||
It's been some time since I worked on this project and I've kind of lost the
thread. Does this code have to be added to the code you previously sent (on the 17th of Aug) or should it work by itself? I keyed it in and it doesn't seem to work. Here's some additional info about what I'm trying to do: I have a worksheet that serves as a purchase card log for purchases made with a government credit card. Data must be entered in columns A thorugh M. Columns F, H, K, and L currently contain drop down lists (Data/Validation/List) containing dates derived from named ranges on another sheet. These are the columns that I would like to contain the combo boxes. I would like the combo boxes to appear only when clicked into, then allow the user to select a date from a list that starts with today's date, (but you can move forward or backward through the dates, or type in a date rather than select from the list). I would like the user to be able to tab to the next column as well as click into the next column. thanks for all your help so far. jan buckley "Dave Peterson" wrote: That was the second one: If to the right one: Private Sub ComboBox1_Change() ActiveCell.Value = Me.ComboBox1.Value ActiveCell.Offset(0, 1).Activate End Sub But I gotta believe that your columns are limited--say B:Z. When you enter data in B3, go to C3, ..., but when you get to Z3, go to B4???? If that's ok, you could change that sub: If to the right one: Private Sub ComboBox1_Change() ActiveCell.Value = Me.ComboBox1.Value if activecell.column = range("z1").column then cells(activcell.row+1,"B").activate else ActiveCell.Offset(0, 1).Activate end if End Sub Change z1 to the correct column (the row won't matter). And change "B" to the first column in the range. Jan Buckley wrote: The "next cell" will always be one to the right. Can you write the code for that? You guys sure are patient with us beginners, and I really appreciate it. "Dave Peterson" wrote: If the next cell is down one: Private Sub ComboBox1_Change() ActiveCell.Value = Me.ComboBox1.Value ActiveCell.Offset(1, 0).Activate End Sub If to the right one: Private Sub ComboBox1_Change() ActiveCell.Value = Me.ComboBox1.Value ActiveCell.Offset(0, 1).Activate End Sub Maybe????? Jan Buckley wrote: Dave, this works great, can't tell you how much I've learned during this little exercise. But is there any way that the user can TAB to the next cell (after selecting the date from the combo box) rather than having to CLICK into it? Thanks. "Jan Buckley" wrote: JE: That sounds like it would work much better but, not being a programmer, I don't know how to accomplish it. Can you help? "JE McGimpsey" wrote: One way: For 1 combobox, add this to your worksheet code module (adjust "A1" to suit): Private Sub Worksheet_SelectionChange( _ ByVal Target As Range) ComboBox1.Visible = _ Not (Intersect(Target, Range("A1")) Is Nothing) End Sub However, if you've got 500 rows that require comboboxes, I'd consider rethinking the approach. Perhaps using a single combobox and positioning it at the appropriate cell whenever one of the cells is selected, then placing the value of the combobox into the selected cell rather than hardcoding it. In article , "Jan Buckley" wrote: I hate to bother you again, but is there a way to "hide" the combo box until you click or tab into the cell where it resides? I have four columns (containing up to 500 rows each) on my spreadsheet that will require the combo boxes and it looks 'ugly' with them all showing. Thanks. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I make other cells dependent on my drop down list? | Excel Discussion (Misc queries) | |||
Trying to Create a Conditional Drop down list | Excel Worksheet Functions | |||
How do you create a drop down list? | Excel Discussion (Misc queries) | |||
edit a drop down list | Excel Discussion (Misc queries) | |||
Drop dow list complication | Excel Discussion (Misc queries) |