From Range of Cell to Combo Box by key-in <enter>

[naydeenmonzon]

Hi Folks,

Not sure if this is possible but I want to be able to activate my combo box after key <enter> from a certain range of cell.
Basically if hit <enter> anywhere from C2:G29 the text line will move to the combo box.

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window


The script will run when you enter any value in any cell in the range you mentioned.
You must have a Combobox named ComboBox1 on the active sheet.
I also added another feature.
If you double click on Range("A1") of active sheet the ComboBox will be emptied.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  12/4/2020  12:01:34 AM  EST
Cancel = True
If Target.Address = Range("A1").Address Then ActiveSheet.ComboBox1.Clear
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  12/4/2020  12:01:34 AM  EST
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("G2:G29")) Is Nothing Then
ComboBox1.AddItem Target.Value
End If
End Sub