+ Reply to Thread
Results 1 to 16 of 16

How to make drop-down list of dates start with current date?

  1. #1
    Jan Buckley
    Guest

    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. #2
    JE McGimpsey
    Guest

    Re: How to make drop-down list of dates start with current date?

    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 <[email protected]>,
    "Jan Buckley" <[email protected]> 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. #3
    Jan Buckley
    Guest

    Re: How to make drop-down list of dates start with current date?

    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 <[email protected]>,
    > "Jan Buckley" <[email protected]> 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. #4
    JE McGimpsey
    Guest

    Re: How to make drop-down list of dates start with current date?

    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 <[email protected]>,
    "Jan Buckley" <[email protected]> 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. #5
    Jan Buckley
    Guest

    Re: How to make drop-down list of dates start with current date?

    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 <[email protected]>,
    > "Jan Buckley" <[email protected]> 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. #6
    JE McGimpsey
    Guest

    Re: How to make drop-down list of dates start with current date?

    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 <[email protected]>,
    "Jan Buckley" <[email protected]> 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. #7
    Jan Buckley
    Guest

    Re: How to make drop-down list of dates start with current date?

    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 <[email protected]>,
    > "Jan Buckley" <[email protected]> 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. #8
    Jan Buckley
    Guest

    Re: How to make drop-down list of dates start with current date?

    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 <[email protected]>,
    > "Jan Buckley" <[email protected]> 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. #9
    JE McGimpsey
    Guest

    Re: How to make drop-down list of dates start with current date?

    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 <[email protected]>,
    "Jan Buckley" <[email protected]> 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. #10
    Jan Buckley
    Guest

    Re: How to make drop-down list of dates start with current date?

    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 <[email protected]>,
    > "Jan Buckley" <[email protected]> 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. #11
    Dave Peterson
    Guest

    Re: How to make drop-down list of dates start with current date?

    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 <[email protected]>,
    > > "Jan Buckley" <[email protected]> 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. #12
    Jan Buckley
    Guest

    Re: How to make drop-down list of dates start with current date?

    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 <[email protected]>,
    > > "Jan Buckley" <[email protected]> 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. #13
    Dave Peterson
    Guest

    Re: How to make drop-down list of dates start with current date?

    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 <[email protected]>,
    > > > "Jan Buckley" <[email protected]> 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. #14
    Jan Buckley
    Guest

    Re: How to make drop-down list of dates start with current date?

    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 <[email protected]>,
    > > > > "Jan Buckley" <[email protected]> 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. #15
    Dave Peterson
    Guest

    Re: How to make drop-down list of dates start with current date?

    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 <[email protected]>,
    > > > > > "Jan Buckley" <[email protected]> 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. #16
    Jan Buckley
    Guest

    Re: How to make drop-down list of dates start with current date?

    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 <[email protected]>,
    > > > > > > "Jan Buckley" <[email protected]> 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1