·
15 days ago
hi, thanks for your code. I would like to autocomplete and make multiple selections in a drop down list. Can you help me please?, thank you
How to autocomplete when typing in Excel drop down list?
If you have a data validation drop down list with large items, you need to scroll up and down in the list just for finding the proper one, or type the whole word into the list box directly. If there is method for allowing to auto complete when typing the first letter in the drop down list, everything will become easier. This tutorial will provide a VBA method to help you achieve it.
Autocomplete when typing in drop down list with VBA code
More tutorials for drop down list...
Autocomplete when typing in drop down list with VBA code
Please do as follows to make a drop down list autocomplete after typing corresponding letters in the cell.
Firstly, you need to insert a combo box into the worksheet and change its properties.
1. Open the worksheet that contains the drop down list cell you want to make it autocomplete.
2. Before inserting a Combo box, you need to add the Developer tab to the Excel ribbon. If the Developer tab is showing on your ribbon, shift to step 3. Otherwise, do as follows: Click File > Options to open the Options window. In this Excel Options window, click Customize Ribbon in the left pane, check the Developer box, and then click the OK button. See screenshot:
3. Click Developer > Insert > Combo Box (ActiveX Control).
4. Draw a combo box in current worksheet. Right click it and then select Properties from the right-clicking menu.
5. In the Properties dialog box, please replace the original text in the (Name) field with TempCombo.
6. Turn off the Design Mode by clicking Developer > Design Mode.
Then, apply the below VBA code
7. Right click on current sheet tab and click View Code from the context menu. See screenshot:
8. In the opening Microsoft Visual Basic for Applications window, please copy and paste the below VBA code into the worksheet’s Code window.
VBA code: Autocomplete when typing in drop down list
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Update by Extendoffice: 2020/01/16
Dim xCombox As OLEObject
Dim xStr As String
Dim xWs As Worksheet
Dim xArr
Set xWs = Application.ActiveSheet
On Error Resume Next
Set xCombox = xWs.OLEObjects("TempCombo")
With xCombox
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
If Target.Validation.Type = 3 Then
Target.Validation.InCellDropdown = False
Cancel = True
xStr = Target.Validation.Formula1
xStr = Right(xStr, Len(xStr) - 1)
If xStr = "" Then Exit Sub
With xCombox
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 5
.Height = Target.Height + 5
.ListFillRange = xStr
If .ListFillRange = "" Then
xArr = Split(xStr, ",")
Me.TempCombo.List = xArr
End If
.LinkedCell = Target.Address
End With
xCombox.Activate
Me.TempCombo.DropDown
End If
End Sub
Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Select Case KeyCode
Case 9
Application.ActiveCell.Offset(0, 1).Activate
Case 13
Application.ActiveCell.Offset(1, 0).Activate
End Select
End Sub
9. Press Alt + Q keys simultaneously to close the Microsoft Visual Basic Applications window.
From now on, when click on a drop down list cell, the drop down list will prompt automatically. You can start to type in the letter to make the corresponding item complete automatically in selected cell. See screenshot:
Note: This code does not work for merged cells.
Easily create drop-down list with checkboxes in Excel:
The Drop-down List with Check Boxes utility of Kutools for Excel can help you easily create drop-down list with checkboxes in a specified range, current worksheet, current workbook or all opened workbooks based on your needs.
Download and try it now! (
30-day free trail)
Related articles:
How to create drop down list with multiple checkboxes in Excel?
Many Excel users tend to create drop down list with multiple checkboxes in order to select multiple items from the list per time. Actually, you can’t create a list with multiple checkboxes with Data Validation. In this tutorial, we are going to show you two methods to create drop down list with multiple checkboxes in Excel. This tutorial provides the method to solve the problem.
Create drop down list from another workbook in Excel
It is quite easy to create a data validation drop down list among worksheets within a workbook. But if the list data you need for the data validation locates in another workbook, what would you do? In this tutorial, you will learn how to create a drop fown list from another workbook in Excel in details.
Create a searchable drop down list in Excel
For a drop down list with numerous values, finding a proper one is not an easy work. Previously we have introduced a method of auto completing drop down list when enter the first letter into the drop down box. Besides the autocomplete function, you can also make the drop down list searchable for enhancing the working efficiency in finding proper values in the drop down list. For making drop down list searchable, try the method in this tutorial.
Auto populate other cells when selecting values in Excel drop down list
Let’s say you have created a drop down list based on the values in cell range B8:B14. When you selecting any value in the drop down list, you want the corresponding values in cell range C8:C14 be automatically populated in a selected cell. For solving the problem, the methods in this tutorial will do you a favor.
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment...
The comment will be refreshed after 00:00.
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.Hello, is there a chance to add dependency to select date and in the next column to the proposed choices?
-
To post as a guest, your comment is unpublished.como hago para agregar esta función=INDIRECTO("tabla1[columna]")al código
-
To post as a guest, your comment is unpublished.I tried to use this method. I added the code. After that I add the range needed. Looks all fine. Then I test and the combobox dissapears directly after filling it. So the code works, but its not usable. What do I do wrong?Kind regards, Danielle
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.Hi I have some difficulties with my excel work. I would like to know if there is a method to autofill the cell I desire?Example1 = san pedro2 = santa rosa3 = calamba4 = san pabloand so on.I have a list with correspoding numbers. If I input number 1 in Cell A5 I would like it to automatically fill the Cell B5 with its corresponding list name (san pedro) and if I input 3 in A5, B5 will be = calamba.thank you. for the help, Im having trouble to find a solution to this hope to find answers.Thank you and God Bless.
-
To post as a guest, your comment is unpublished.Hi Guys,
I have made 2 different drop down list searchable in 1 sheet, the fist is working well but the second drop down list is showing me the formula of indirect as the second drop down list is indirect of the first. I have apply the VBA with combo and so on but still the second list is showing me the formula. how to I fix this? -
To post as a guest, your comment is unpublished.Hi Guys,
I have made 2 different drop down list searchable in 1 sheet, the fist is working well but the second drop down list is showing me the formula of indirect as the second drop down list is indirect of the first. I have apply the VBA with combo and so on but still the second list is showing me the formula. how to I fix this?
-
To post as a guest, your comment is unpublished.Hello, I have a problem with this method and I already use a list validated with an indirect function to refer to the range of a table and when applying this macro it shows me only the indirect formula in the list! How could I solve this problem?
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.Hello, is it possible to replicate this in a complete column? I mean that each individual row of a column has this possibility or at least 1000 rows in a simple way. Thank you!
-
To post as a guest, your comment is unpublished.If you use this method with one combobox/sheet i guess it's flawless.
I used this method with three comboboxes on one sheet with 3 different ListFillRange.
Dropdown keep showing up on different combobox than what i'm typing in.
1. I type and select correctly from the first.
2. When i go to second combo, the third dropdown pops up.(I have to select the"", as it is still empty)
then i can search my second combobox.
3. When i go to the third, the first dropdown pops up, I reselect the only item(as there is only one that was already selected in the first step)
After this i can add the content in the third dropdown.
All subs are on sheet1's vba window.
Now i'm trying to spit code in different modules, but can't really make it work.
Search works, but i always have to push the dropdown arrow to see available results to select. Dropdown doesn't pop up at all.
using 365, but i have to make "sheet" work on excel 2010 -
To post as a guest, your comment is unpublished.The command works when first applied. But after saving the file, when i reopen it the drop down itself disappears.
-
To post as a guest, your comment is unpublished.Hi Rohit,
After applying the code, please save the workbook as an Excel Macro-Enabled Workbook before closing the file.
-
-
To post as a guest, your comment is unpublished.I get an error message (1004) - the debug highlights ‘If Target.Validation.Type =3 Then’
for info .. I use excel 2010
-
To post as a guest, your comment is unpublished.Hi,
I tried the VBA in Excel 2010 and it works well.
The drop down list should be data validation drop-down list which is created by clicking Data > Data Validation....-
To post as a guest, your comment is unpublished.It's work in cell with list but when i move or tap or select any cell with no list ..the up message talked you about always show..
-
-
-
To post as a guest, your comment is unpublished.Hi! Thanks for this code!
I'm getting a compile error saying "variable not defined", it highlights the "cancel = true" line
-
To post as a guest, your comment is unpublished.Hi Carlos,
Which Excel version are you using?-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.
-
-
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.Hi,
Is it possible to show the matches with the caracter your typping using this code and format of combox?
Example:
You have a list with Onion, Black Onion, Green Onion, Red Onion...
And in the combox you type: "On", so the drop down list show all the values existing on the list that matchs with "On".
Thanks -
To post as a guest, your comment is unpublished.When I applied this code to my worksheet, the Undo function is disabled (but only for this sheet in the workbook) -- is there a way to fix this so that undo can still be used?
-
To post as a guest, your comment is unpublished.Hi Reinaldo Yajure,
Please apply the below VBA code. The problem is fixed. Sorry for the inconvenience.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Update by Extendoffice: 2020/05/19
Dim xCombox As OLEObject
Dim xStr As String
Dim xWs As Worksheet
Dim xArr
Set xWs = Application.ActiveSheet
On Error Resume Next
If Target.Validation.Type = 3 Then
Target.Validation.InCellDropdown = False
Cancel = True
xStr = Target.Validation.Formula1
xStr = Right(xStr, Len(xStr) - 1)
If xStr = "" Then Exit Sub
Set xCombox = xWs.OLEObjects("TempCombo")
With xCombox
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
With xCombox
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 5
.Height = Target.Height + 5
.ListFillRange = xStr
If .ListFillRange = "" Then
xArr = Split(xStr, ",")
Me.TempCombo.List = xArr
End If
.LinkedCell = Target.Address
End With
xCombox.Activate
Me.TempCombo.DropDown
End If
End Sub
Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Select Case KeyCode
Case 9
Application.ActiveCell.Offset(0, 1).Activate
Case 13
Application.ActiveCell.Offset(1, 0).Activate
End Select
End Sub
-
To post as a guest, your comment is unpublished.Hi there, this is a great solution, but for some reason, in the original code, the combox was hidden when the cell with the data validation was not active, and only visible when the cell was selected.With this code, we get undo back, which is perfect, but the combox does not auto-hide again when you click off.Do you have a solution for this?Thanks,Alistair
-
-
-
To post as a guest, your comment is unpublished.is there any way to increase the list size as it is showing up only 10 line items as i have 12 lines
-
To post as a guest, your comment is unpublished.How can I Use Tab/Arrows/Enter to move to another cell once the current cell is filled? Thanks
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.Hi Guys, are you able to share a code which i can use so that the autocomplete is used on the same column i.e H in every sheet within the workbook.
-
To post as a guest, your comment is unpublished.Hi,
If you only want the column H to be affected, please try the below VBA code. But it is only available in one worksheet. For multiple sheets, you need to add the code to each sheet code window. Hope I can help.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Update by Extendoffice: 2019/8/14
Dim xCombox As OLEObject
Dim xStr As String
Dim xWs As Worksheet
Dim xArr
Dim xRgStr As String
Dim xRg As Range
xRgStr = "H:H"
Set xWs = Application.ActiveSheet
On Error Resume Next
Set xCombox = xWs.OLEObjects("TempCombo")
With xCombox
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
Set xRg = Intersect(Range(xRgStr), Target)
If xRg Is Nothing Then Exit Sub
If Target.Validation.Type = 3 Then
Target.Validation.InCellDropdown = False
Cancel = True
xStr = Target.Validation.Formula1
xStr = Right(xStr, Len(xStr) - 1)
If xStr = "" Then Exit Sub
With xCombox
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 5
.Height = Target.Height + 5
.ListFillRange = xStr
If .ListFillRange = "" Then
xArr = Split(xStr, ",")
Me.TempCombo.List = xArr
End If
.LinkedCell = Target.Address
End With
xCombox.Activate
Me.TempCombo.DropDown
End If
End Sub
Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Select Case KeyCode
Case 9
Application.ActiveCell.Offset(0, 1).Activate
Case 13
Application.ActiveCell.Offset(1, 0).Activate
End Select
End Sub
-
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.Woooooooowwwwww very useful, thankyou for sharing, god bless you
-
To post as a guest, your comment is unpublished.Love this! Thanks so much. Got a small issue though. I like to tab from one cell to the next, but when I get to the cell with the drop down list, I can tab into the cell with no problem, but once I'm in, it won't let me tab back out. I have to physically move the mouse over and click into the next cell. Any fix for this? Kind of disrupts my rhythm, which is why I loved this autocomplete function in the first place. ;o)
-
To post as a guest, your comment is unpublished.This is a great find, however, I have one little issue: I like to tab through the cells as I am entering data. I can tab into the cell with the drop down menu just fine, but once I'm in that cell, it stops me from exiting the cell with a tab. I have to physically move the mouse to the next cell and click on it. Is there a fix to be able to simply tab in and tab out of the autocomplete cell? Thanks.
-
To post as a guest, your comment is unpublished.I am using this code and a data validation with drop down lists for easy acces to what i need because i need the data to remain in excel boxes for other formulas to use it. However one of the combo boxes has almost 1500 items in the drop down and I'd like the user to be able to start typing a word in the combo box and get a list of suggestions to choose from based on the word entered for example if they type 'joh' in the combo box I want to show them a list of all the choices in the drop down that contains 'joh' so they can select the one they want. Now i can find the first 'joh' but it shows me like a list with what is near this 'joh', not specific all that contain 'joh'. Can i modify this code so can be used for what i want? Thank you and please help
-
To post as a guest, your comment is unpublished.The Combobox works fine when 1-MatchEntryComplete is selected ,when I changed the selection to 2-fmMatchEntryNone in the Combobox properties it did not work ,i.e It does not auto select as I start typing. Is there a fix for this?
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.The code working but whenever I click on cell there the drop list auto prompt ,this make me unable to copy the contents and paste somewhere , how can I enable the copy and paste ?
-
To post as a guest, your comment is unpublished.
-
-
To post as a guest, your comment is unpublished.I try copy the whole cell of droplisting after all selection , but whenever I click on cell it auto show the listing , this has cost me unable to copy the cells and paste the contents somewhere , any solutions , pls help ?
-
To post as a guest, your comment is unpublished.
-
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.Hey guys, I'm having an issue where it doesn't let me type what I want to search for letter by letter. It just shows the whole word automatically and highlighted, and I start to type it moves it to the next closest word. is there anyway to not have it highlight the whole thing?
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.hi when i use the VBA code on a column that has dates it changes it to a number. can anyone help
-
To post as a guest, your comment is unpublished.Hi, Thaks for the code. Could I check if this formula can include an ignore blank function? This code took away my ignore blank function for my data validation. Please advise thanks
-
To post as a guest, your comment is unpublished.Hey, thanks for this! I have managed to get it to work, but like in Cartson's comment: I need to keep selections to the list. It populates ok, but if I enter a word not in the list, it still accepts it. The VBA code you replied with doesn't work unfortunately
Like in Data Validation/locked cell, can it create some error or not allow the typed entry not in the list?
Any help is much appreciated-
To post as a guest, your comment is unpublished.Hi Ben,
Sorry there are mistakes in the previous reply.
After inserting the combo box, open its Properties window, change the Name to TempCombo, and then select 2 - fmStyleDropDownList from the Style field, and finally apply the below code. From now on, it won't allow entering word not in the list.
Dim xRg As Range
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim xCombox As OLEObject
Dim xStr As String
Dim I As Long
Dim xWs As Worksheet
Set xWs = Application.ActiveSheet
On Error Resume Next
Set xCombox = xWs.OLEObjects("TempCombo")
With xCombox
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
If Target.Validation.Type = 3 Then
Target.Validation.InCellDropdown = False
Cancel = True
xStr = Target.Validation.Formula1
xStr = Right(xStr, Len(xStr) - 1)
If xStr = "" Then Exit Sub
Set xRg = Target
With xCombox
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 5
.Height = Target.Height + 5
.ListFillRange = xStr
.LinkedCell = Target.Address
End With
xCombox.Activate
Me.TempCombo.DropDown
End If
End Sub
Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim xSel As Range
On Error Resume Next
Select Case KeyCode
Case 13
xRg.Offset(0, 1).Select
End Select
End Sub-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.Hello Crystal,
I have tried to do this... but it doesn't work... I have 4 lists 2 independent and the other two depend on others... It only works with the first list
-
-
-
To post as a guest, your comment is unpublished.Really great tutorial, thanks. Do you know how I can adapt this code to get it to work for merged cells? Thanks again.
-
To post as a guest, your comment is unpublished.Hi every one i noticed a few questions asking if there is a way to limit this to one column but i didn't notice any answers, was this ever figured out? i have multiple drop downs in my work sheet and they are all being affected by the macro where i only want column H to be affected. Any help with this would be greatly appreciated.
-
To post as a guest, your comment is unpublished.Hi Rob K,
The below VBA code can help you solve the problem, please have a try and thank you for your comment.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Update by Extendoffice: 2019/8/14
Dim xCombox As OLEObject
Dim xStr As String
Dim xWs As Worksheet
Dim xArr
Dim xRgStr As String
Dim xRg As Range
xRgStr = "H:H"
Set xWs = Application.ActiveSheet
On Error Resume Next
Set xCombox = xWs.OLEObjects("TempCombo")
With xCombox
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
Set xRg = Intersect(Range(xRgStr), Target)
If xRg Is Nothing Then Exit Sub
If Target.Validation.Type = 3 Then
Target.Validation.InCellDropdown = False
Cancel = True
xStr = Target.Validation.Formula1
xStr = Right(xStr, Len(xStr) - 1)
If xStr = "" Then Exit Sub
With xCombox
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 5
.Height = Target.Height + 5
.ListFillRange = xStr
If .ListFillRange = "" Then
xArr = Split(xStr, ",")
Me.TempCombo.List = xArr
End If
.LinkedCell = Target.Address
End With
xCombox.Activate
Me.TempCombo.DropDown
End If
End Sub
Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Select Case KeyCode
Case 9
Application.ActiveCell.Offset(0, 1).Activate
Case 13
Application.ActiveCell.Offset(1, 0).Activate
End Select
End Sub-
To post as a guest, your comment is unpublished.Hi,I am trying to pull that list data from another sheet but it does not work. below is my codePrivate Sub Worksheet_SelectionChange(ByVal Target As Range)
'Update by Extendoffice: 20190812
Dim xCombox As OLEObject
Dim xStr As String
Dim xWs As Worksheet
Dim xArr
Dim xRgStr As String
Dim xRg As Range
xRgStr = "MAINInput!$D:$D"
'MAINInput = sheet name where the list is available
Set xWs = Application.ActiveSheet
On Error Resume Next
Set xCombox = xWs.OLEObjects("cmbEmployee")
With xCombox
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
Set xRg = Intersect(Range(xRgStr), Target)
If xRg Is Nothing Then Exit Sub
If Target.Validation.Type = 3 Then
Target.Validation.InCellDropdown = False
Cancel = True
xStr = Target.Validation.Formula1
xStr = Right(xStr, Len(xStr) - 1)
If xStr = "" Then Exit Sub
With xCombox
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 5
.Height = Target.Height + 5
.ListFillRange = xStr
If .ListFillRange = "" Then
xArr = Split(xStr, ",")
Me.cmbEmployee.List = xArr
End If
.LinkedCell = Target.Address
End With
xCombox.Activate
Me.cmbEmployee.DropDown
End If
End Sub
Private Sub cmbEmployee_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Select Case KeyCode
Case 9
Application.ActiveCell.Offset(0, 1).Activate
Case 13
Application.ActiveCell.Offset(1, 0).Activate
End Select
End Sub -
To post as a guest, your comment is unpublished.Thank you for this fix! One quick question: I running this code across multiple worksheets (31 to be exact) but it is only affecting one. I have the code the same for each worksheet limiting the columns but it doesn't seem to be working on the other sheets. Thanks in advance!
-
-
To post as a guest, your comment is unpublished.Hi Rob K,
The below VBA code can help you solve the problem. Thank you for your comemnt.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Update by Extendoffice: 20190812
Dim xCombox As OLEObject
Dim xStr As String
Dim xWs As Worksheet
Dim xArr
Dim xRgStr As String
Dim xRg As Range
xRgStr = "H:H" 'The range of cells containg drop-down lists you will make them autocomplete
Set xWs = Application.ActiveSheet
On Error Resume Next
Set xCombox = xWs.OLEObjects("TempCombo")
With xCombox
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
Set xRg = Intersect(Range(xRgStr), Target)
If xRg Is Nothing Then Exit Sub
If Target.Validation.Type = 3 Then
Target.Validation.InCellDropdown = False
Cancel = True
xStr = Target.Validation.Formula1
xStr = Right(xStr, Len(xStr) - 1)
If xStr = "" Then Exit Sub
With xCombox
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 5
.Height = Target.Height + 5
.ListFillRange = xStr
If .ListFillRange = "" Then
xArr = Split(xStr, ",")
Me.TempCombo.List = xArr
End If
.LinkedCell = Target.Address
End With
xCombox.Activate
Me.TempCombo.DropDown
End If
End Sub
Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Select Case KeyCode
Case 9
Application.ActiveCell.Offset(0, 1).Activate
Case 13
Application.ActiveCell.Offset(1, 0).Activate
End Select
End Sub
-
-
To post as a guest, your comment is unpublished.When I paste the VBA code, do I need to change the Worksheet code editor to the Worksheet and Before Double Click? I changed those 2 fields and it populated codes into the code editor screen. So where do I paste the VBA code? after those? Before those? do I delete those 2 codes that autofill and then paste? I tried that but then the Worksheet changed back to General and Declarations and the code doesn't make my cells autofill. What am I doing wrong???
-
To post as a guest, your comment is unpublished.How can i use same list for another combo box, do i need to write same code for another combo box, as i require 10 combo box
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.How can the code be modified such that pressing SHIFT + TAB moves the cursor left in the row?
-
To post as a guest, your comment is unpublished.Hi Anthony,
Sorry can't help you with that. Thank you for your comment.-
To post as a guest, your comment is unpublished.
-
-
-
To post as a guest, your comment is unpublished.But what if i have dynamic list ? How can i provide the input to the combobox and refresh everytime the data is been added?
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.
-
-
To post as a guest, your comment is unpublished.Anyone looking for a simple dropdown input form in excell based on a list; i used Data.. "Data Validation" and then choose Allow: "List" and pointed the source at the list.
This does not do the auto complete but does avoid any macros or dev and just uses native simple Excel features. -
To post as a guest, your comment is unpublished.