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

naydeenmonzon

New Member
Joined
Dec 3, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,651
Office Version
  1. 2013
Platform
  1. Windows
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.

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,132,786
Messages
5,655,298
Members
418,187
Latest member
polks111

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top