Drop down lists (MS Excel 2000/SP3)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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:

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

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

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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…

Reply | Quote

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;-))

Reply | Quote

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

Reply | Quote

Hans,

Thank you very much for your help. I now have it sorted out. It works without code!

Reply | Quote

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.

Reply | Quote

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

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.

Reply | Quote

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

When can I visit for lessons? Very soon?

Reply | Quote