Hi iRounak - or maybe I should address you as uRounak?
I had run across this the other day on Contextures, but was put off by the doubleclicking. I'm not sure I even downloaded it.
However, I did tonight. Weird thing is, it seems to me that
in it andCode:Private Sub TempCombo_KeyDown(ByVal _ KeyCode As MSForms.ReturnInteger, _ ByVal Shift As Integer) ... Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) ... Private Sub Worksheet_SelectionChange(ByVal Target As Range) ...
DataValComboboxClick.zip has only
As I have worked with DataValComboboxClick.zip, my experience is that it works quite well by keyboard alone or with the mouse. If I cursor to a cell, it goes into entry mode, I can up and down arrow to select values, or use the autocomplete, then return or tab out of the cell. No clicking required.Code:Private Sub TempCombo_KeyDown(ByVal _ KeyCode As MSForms.ReturnInteger, _ ByVal Shift As Integer) ... Private Sub Worksheet_SelectionChange(ByVal Target As Range) ...
(admittedly, the machine I'm on has Excel 2002, and I've not had a chance to test this on 2003 or 2007 yet)
However, I also experienced a problem when I would use the enter key on the last row in the cells with validation. Excel would crash. Every time. I stepped through the code, fiddled with error handling, nothing would prevent the crash. I did find that modifying this part solved my problem:Actually this solves a problem I had at work, which led me to Contextures to begin with.Code:Private Sub TempCombo_KeyDown(ByVal _ KeyCode As MSForms.ReturnInteger, _ ByVal Shift As Integer) Debug.Print KeyCode 'Hide combo box and move to next cell on Enter and Tab Select Case KeyCode Case 9, 13, 17 'tab, enter, ctrl (ie ctrl enter) select the same cell ActiveCell.Offset(0, 0).Activate Case 37 ' left arrow - move left ActiveCell.Offset(0, -1).Activate Case 39 ' right arrow - move right ActiveCell.Offset(0, 1).Activate Case 46 ' Del (8 would be back) - clear cell ActiveCell.Value = "" Case Else 'do nothing End Select End Sub