·
7 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
كيفية الإكمال التلقائي عند الكتابة في قائمة Excel المنسدلة؟
إذا كانت لديك قائمة منسدلة للتحقق من صحة البيانات تحتوي على عناصر كبيرة ، فستحتاج إلى التمرير لأعلى ولأسفل في القائمة فقط للعثور على الكلمة المناسبة ، أو كتابة الكلمة بأكملها في مربع القائمة مباشرةً. إذا كانت هناك طريقة للسماح بالإكمال التلقائي عند كتابة الحرف الأول في القائمة المنسدلة ، فسيصبح كل شيء أسهل. سيوفر هذا البرنامج التعليمي طريقة VBA لمساعدتك في تحقيقها.
الإكمال التلقائي عند الكتابة في القائمة المنسدلة برمز VBA
المزيد من البرامج التعليمية للقائمة المنسدلة ...
الإكمال التلقائي عند الكتابة في القائمة المنسدلة برمز VBA
يرجى القيام بما يلي لإكمال القائمة المنسدلة تلقائيًا بعد كتابة الأحرف المقابلة في الخلية.
أولاً ، تحتاج إلى إدراج مربع تحرير وسرد في ورقة العمل وتغيير خصائصه.
1. افتح ورقة العمل التي تحتوي على خلية القائمة المنسدلة التي تريد إكمالها تلقائيًا.
2. قبل إدراج مربع التحرير والسرد ، تحتاج إلى إضافة علامة التبويب Developer إلى شريط Excel. إذا كانت علامة التبويب المطور تظهر على الشريط الخاص بك ، انتقل إلى الخطوة 3. وإلا ، فقم بما يلي: انقر فوق قم بتقديم > الخيارات لفتح الخيارات نافذة او شباك. في هذا خيارات إكسيل الإطار، انقر فوق تخصيص الشريط في الجزء الأيمن ، تحقق من المطور مربع ، ثم انقر فوق OK زر. انظر لقطة الشاشة:
3. انقر المطور > إدراج > صندوق التحرير والسرد (عنصر تحكم ActiveX).
4. ارسم مربع تحرير وسرد في ورقة العمل الحالية. انقر بزر الماوس الأيمن فوقه ثم حدد عقارات من قائمة النقر بزر الماوس الأيمن.
5. في ال عقارات في مربع الحوار ، يرجى استبدال النص الأصلي بتنسيق (اسم) المجال مع TempCombo.
6. إيقاف تشغيل وضع التصميم بالضغط المطور > وضع التصميم.
بعد ذلك ، قم بتطبيق رمز VBA أدناه
7. انقر بزر الماوس الأيمن فوق علامة تبويب الورقة الحالية وانقر فوق عرض الرمز من قائمة السياق. انظر لقطة الشاشة:
8. في الافتتاح ميكروسوفت فيسوال باسيك للتطبيقات نافذة ، يرجى نسخ ولصق رمز فبا أدناه في نافذة كود ورقة العمل.
كود فبا: الإكمال التلقائي عند الكتابة في القائمة المنسدلة
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. صحافة قديم + Q مفاتيح في نفس الوقت لإغلاق تطبيقات Microsoft Visual Basic نافذة.
من الآن فصاعدًا ، عند النقر فوق خلية قائمة منسدلة ، ستظهر مطالبة القائمة المنسدلة تلقائيًا. يمكنك البدء في كتابة الحرف لإكمال العنصر المقابل تلقائيًا في الخلية المحددة. انظر لقطة الشاشة:
ملاحظة: هذا الرمز لا يعمل مع الخلايا المدمجة.
أنشئ بسهولة قائمة منسدلة مع مربعات الاختيار في Excel:
إن قائمة منسدلة مع مربعات اختيار فائدة كوتولس ل إكسيل يمكن أن يساعدك في إنشاء قائمة منسدلة بسهولة مع مربعات اختيار في نطاق محدد أو ورقة عمل حالية أو مصنف حالي أو جميع المصنفات المفتوحة بناءً على احتياجاتك.
قم بتنزيله وجربه الآن! (تجربة مجانية لمدة 30 يومًا)
مقالات ذات صلة:
كيفية إنشاء قائمة منسدلة مع مربعات اختيار متعددة في إكسيل؟
يميل العديد من مستخدمي Excel إلى إنشاء قائمة منسدلة مع مربعات اختيار متعددة من أجل تحديد عناصر متعددة من القائمة في كل مرة. في الواقع ، لا يمكنك إنشاء قائمة بمربعات اختيار متعددة باستخدام التحقق من صحة البيانات. في هذا البرنامج التعليمي ، سوف نعرض لك طريقتين لإنشاء قائمة منسدلة مع مربعات اختيار متعددة في Excel. يوفر هذا البرنامج التعليمي طريقة حل المشكلة.
قم بإنشاء قائمة منسدلة من مصنف آخر في Excel
من السهل جدًا إنشاء قائمة منسدلة للتحقق من صحة البيانات بين أوراق العمل داخل المصنف. ولكن إذا كانت بيانات القائمة التي تحتاجها للتحقق من صحة البيانات موجودة في مصنف آخر ، فماذا ستفعل؟ في هذا البرنامج التعليمي ، ستتعلم كيفية إنشاء قائمة منسدلة من مصنف آخر في Excel بالتفصيل.
قم بإنشاء قائمة منسدلة قابلة للبحث في Excel
بالنسبة للقائمة المنسدلة ذات القيم المتعددة ، فإن العثور على قائمة مناسبة ليس بالأمر السهل. قدمنا سابقًا طريقة قائمة منسدلة للإكمال التلقائي عند إدخال الحرف الأول في المربع المنسدل. إلى جانب وظيفة الإكمال التلقائي ، يمكنك أيضًا جعل القائمة المنسدلة قابلة للبحث لتحسين كفاءة العمل في العثور على القيم المناسبة في القائمة المنسدلة. لجعل القائمة المنسدلة قابلة للبحث ، جرب الطريقة الموجودة في هذا البرنامج التعليمي.
ملء الخلايا الأخرى تلقائيًا عند تحديد القيم في القائمة المنسدلة في Excel
لنفترض أنك قمت بإنشاء قائمة منسدلة بناءً على القيم الموجودة في نطاق الخلايا B8: B14. عند تحديد أي قيمة في القائمة المنسدلة ، فأنت تريد ملء القيم المقابلة في نطاق الخلايا C8: C14 تلقائيًا في خلية محددة. لحل المشكلة ، ستفيدك الطرق الموجودة في هذا البرنامج التعليمي.
أفضل أدوات إنتاجية المكتب
Kutools for Excel يحل معظم مشاكلك ويزيد إنتاجيتك بنسبة 80٪
- إعادة استخدام: أدخل بسرعة الصيغ المعقدة والرسوم البيانية وأي شيء استخدمته من قبل ؛ تشفير الخلايا مع كلمة السر إنشاء قائمة بريدية وإرسال رسائل البريد الإلكتروني ...
- سوبر فورميولا بار (بسهولة تحرير أسطر متعددة من النص والصيغة) ؛ تخطيط القراءة (قراءة وتحرير أعداد كبيرة من الخلايا بسهولة) ؛ لصق في النطاق المصفى...
- دمج الخلايا / الصفوف / الأعمدة دون فقدان البيانات ؛ تقسيم محتوى الخلايا ؛ ادمج الصفوف / الأعمدة المكررة... منع تكرار الخلايا؛ قارن النطاقات...
- حدد مكرر أو فريد صفوف حدد صفوف فارغة (جميع الخلايا فارغة) ؛ البحث الفائق والبحث الغامض في العديد من المصنفات. تحديد عشوائي ...
- نسخة طبق الأصل خلايا متعددة بدون تغيير مرجع الصيغة ؛ إنشاء المراجع تلقائيًا إلى أوراق متعددة أدخل الرموز النقطية، مربعات الاختيار والمزيد ...
- استخراج النص، إضافة نص ، إزالة حسب الموضع ، إزالة الفضاء؛ إنشاء وطباعة المجاميع الفرعية لترحيل الصفحات ؛ التحويل بين محتوى الخلايا والتعليقات...
- سوبر تصفية (حفظ وتطبيق مخططات التصفية على أوراق أخرى) ؛ فرز متقدم حسب الشهر / الأسبوع / اليوم ، التكرار والمزيد ؛ مرشح خاص بواسطة bold، italic ...
- اجمع بين المصنفات وأوراق العمل؛ دمج الجداول على أساس الأعمدة الرئيسية ؛ تقسيم البيانات إلى أوراق متعددة; تحويل دفعة xls و xlsx و PDF...
- أكثر من 300 ميزة قوية. يدعم Office / Excel 2007-2019 و 365. يدعم جميع اللغات. سهولة النشر في مؤسستك أو مؤسستك. الميزات الكاملة نسخة تجريبية مجانية لمدة 30 يومًا. ضمان استرداد الأموال لمدة 60 يومًا.
يجلب Office Tab الواجهة المبوبة إلى Office ، ويجعل عملك أسهل بكثير
- تمكين التحرير والقراءة المبوبة في Word و Excel و PowerPointوالناشر والوصول و Visio والمشروع.
- فتح وإنشاء مستندات متعددة في علامات تبويب جديدة من نفس النافذة ، بدلاً من النوافذ الجديدة.
- يزيد إنتاجيتك بنسبة 50٪ ، ويقلل مئات النقرات بالماوس كل يوم!
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.