Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts

    Drop down lists (MS Excel 2000/SP3)

    I made a transaction record template in Excel. Two items use a drop down list each. After selecting the item entry from the list it is not possible to navigate to another cell with Tab or Enter. Tab & Enter work as expected from other cells.

    Have I missed a Properties item setting or what? I was assured that Tab & Enter should continue to move the focus either to the right or down that I believe are the default settings for these keystrokes.

    Any help would be very much appreciated to sort this problem out.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Drop down lists (MS Excel 2000/SP3)

    What kind of dropdown list are you using? There are three types that I know of for use on a worksheet:

    1. Data validation of type "List". Enter and Tab will work as in other cells.
    2. The dropdown list (combo box) from the "Forms" toolbar. Tab doesn't work, enter lets you return to the cell that was active before you clicked the dropdown list.
    3. The dropdown list (combo box) from the "Control Toolbox". Neither Enter nor Tab works. You must use Esc to get out of it.

  3. #3
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts

    Re: Drop down lists (MS Excel 2000/SP3)

    Thank you for your quick & helpful reply & the information therein. I'll look into what you have told me.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Drop down lists (MS Excel 2000/SP3)

    I assume you have a control toolbox combobox in a cell and if you hit enter you want it to go down to the next cell and if you hit tab you want to go to the right cell.

    The normal behavior for a cell is to that, but you have a combobox which you have to TELL to do this. Add something like this to the combobox events:

    <pre>Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
    ByVal Shift As Integer)
    If KeyCode = 13 Then 'return
    Range(ComboBox1.LinkedCell).Offset(1, 0).Select
    ElseIf KeyCode = 9 Then 'Tab
    Range(ComboBox1.LinkedCell).Offset(0, 1).Select
    End If
    End Sub
    </pre>

    This will move the selection to the cell under the linked cell when enter is hit and got to the cell to the right when tab is hit. Change the name of the combobox as appropriate.

    The code goes into the SHEET object that holds the combobox object.

    Steve

  5. #5
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts

    Re: Drop down lists (MS Excel 2000/SP3)

    Thank you for your reply. I am delighted by the responses to my question.

    As it is 01:10 here in UK I will take this up again in the morning.

  6. #6
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts

    Re: Drop down lists (MS Excel 2000/SP3)

    Many thanks for your help with this. It is very difficult to teach an old dog new tricks so I am pleased to be able to tell you that I have been able to enter your code & with a few stumbles get it to work OK for both combo boxes.

    Can I assume that the same code, suitably modified, work with Calendar Control 9.0 also from the Control Toolbox?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Drop down lists (MS Excel 2000/SP3)

    I'm sure that Steve will have an answer for you, but in the meantime, why don't you try it? It can't take more than a few minutes to find out... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  8. #8
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts

    Re: Drop down lists (MS Excel 2000/SP3)

    Hans,

    Yes you are right that I should try it. But as my efforts to get a handle on VB over several years have been a dismal failure I am seeking the comfort of further guidance. After all I could `break' what has already been achieved.

    On the other hand, in a nice way, you may be telling me it will work! {8;-))

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Drop down lists (MS Excel 2000/SP3)

    The only thing you have to do is replace the name of the combo box by that of the calendar control.

  10. #10
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts

    Re: Drop down lists (MS Excel 2000/SP3)

    Here's what I have found out using the line:

    Private Sub Calendar1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer

    a Compile error appears:

    Procedure declaration does not match description of event or procedure having the same name

    I think I realise that means I have the routine in the wrong place.

  11. #11
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts

    Re: Drop down lists (MS Excel 2000/SP3)

    Hans,

    Thank you very much for your help. I now have it sorted out. <img src=/S/blush.gif border=0 alt=blush width=15 height=15> It works without code!

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Drop down lists (MS Excel 2000/SP3)

    Okay, that's easily remedied. Change KeyCode As MSForms.ReturnInteger to KeyCode As Integer

    <img src=/w3timages/blueline.gif width=33% height=2>

    What I had expected (my mistake), was that you would have let Visual Basic create the first and last line of the procedure, and that you would have copied the "body" of Steve's procedure. You can let VB create the first and last line of an event procedure as follows (try this out on a dummy workbook, not you "real" one):

    Insert a calendar (or other) control on the worksheet.
    Right click the control, and select 'View Code' (or double click the control)
    Visual Basic will have created the skeleton for the default event of the control, usually its Click event.
    Select the event you want to write code for from the dropdown list on the upper right hand corner of the code window, in this case the KeyDon event. For a calendar, you'd see

    Private Sub Calendar1_KeyDown(KeyCode As Integer, ByVal Shift As Integer)

    End Sub

    Now, you can write code to 'flesh out' this skeleton.

  13. #13
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts

    Re: Drop down lists (MS Excel 2000/SP3)

    Thank you for your time trouble & patience for that explanation.

    When can I visit for lessons? Very soon? <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

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