Hello,
I try to write item in combobox1 to autocomplete-with-drop-down-box for many items , but show error subscript out of range
If InStr(1, ComboBox1.Value, vList1(n), vbTextCompare) > 0 Thenany help to overcome this problem,please?
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Subscript out of range to autocomplete items in combobox
0
Answers
0
Selected Answer
Hi again Abdo.
Not sure what you're doing here but the error arises in the ComboBox1_Change() routine behind UserForm1.
It's because you test for an array element vList1(n) but it hass a second dimension so should read vList1(n, 1). A similar error is inyour Select Case Mtch block (corrrected in bold in the code extract below and within the attached file).
Mtch = ""
' loop through array, looking for any matches
For n = LBound(vList1) To UBound(vList1)
' if matched, set Mtch to True
If InStr(1, ComboBox1.Value, vList1(n, 1), vbTextCompare) > 0 Then
' stop on first match
Mtch = vList1(n, 1)
Exit For
End If
Next n
' do actions for whatever was matched
Select Case Mtch
Case vList1(1, 1) ' for SALES INVOICE
TextBox2.Enabled = True
TextBox2.SetFocus
TextBox3.Enabled = False
Case vList1(2, 1) ' For CASH PAID
TextBox2.Enabled = False
TextBox3.Enabled = True
TextBox3.SetFocus
Case vList1(3, 1) ' For CASH RECEIVED
TextBox2.Enabled = True
TextBox3.Enabled = False
TextBox2.SetFocus
' add other cases HERE
Case Else ' if no match
TextBox2.Enabled = False
TextBox3.Enabled = False
End Select
End SubHope this fixes things for you- if so, please mark this Answer as Selected.
Discussion
thanks John.
I still face problem. when I try to select item by arrow from keybord after write in combobox1 then will select item I didn't selected!
the arrow will not pass for specific item .
I still face problem. when I try to select item by arrow from keybord after write in combobox1 then will select item I didn't selected!
the arrow will not pass for specific item .
Abdo M (rep: 26) Jul 20, '26 at 7:03 am
Abdo, Sorry but I don't know what you mean. I fixed your "subscript out of range" problem, right?
If I use my file (did you?), I can select a choice from ComboBox1 and the cursor moves between textboxes (following your actions under Select Case). I can type in the enabled TextBox but you have not written code to handle text changes yet. No error occurs.
I don't understand why you have Sales Invoice Number in that ComboBox1 drop down. Why don't you pick between Paid and Received then populate a second ComboBox with a dropdown of either Sales or Suplier invoice numbers (for Paid or Received amounts)?
If I use my file (did you?), I can select a choice from ComboBox1 and the cursor moves between textboxes (following your actions under Select Case). I can type in the enabled TextBox but you have not written code to handle text changes yet. No error occurs.
I don't understand why you have Sales Invoice Number in that ComboBox1 drop down. Why don't you pick between Paid and Received then populate a second ComboBox with a dropdown of either Sales or Suplier invoice numbers (for Paid or Received amounts)?
John_Ru (rep: 6832) Jul 20, '26 at 8:56 am
I can select a choice from ComboBox1 and the cursor moves between textboxes (following your actions under Select Case)
yes That's correct, but I said when use arrows from keyboard will not move to the others items in combobox , will just select first selection always ,when I try to move to third selection in combobox by arrow from keyboard then just select first selection always . I Don't understand why happens for me when I use arrows from keyboard!
yes That's correct, but I said when use arrows from keyboard will not move to the others items in combobox , will just select first selection always ,when I try to move to third selection in combobox by arrow from keyboard then just select first selection always . I Don't understand why happens for me when I use arrows from keyboard!
Abdo M (rep: 26) Jul 20, '26 at 9:05 am
Abdo. If you use the dropdown to pick item 1 in the ComboBox say, your code gives focus to a textbox. The down key won't work unless you give focus back to the ComboBox (by clicking in it or programmatically, via a textbox procdure). You can use up or down but not beyond last and first items.
That works for me but are you trying to use arrow keys on a numeric key pad rather than the number above the letters? That's nearly always disabled by default but can be enabled.
Again you are straying away from your original Question :--(
That works for me but are you trying to use arrow keys on a numeric key pad rather than the number above the letters? That's nearly always disabled by default but can be enabled.
Again you are straying away from your original Question :--(
John_Ru (rep: 6832) Jul 20, '26 at 9:50 am
but are you trying to use arrow keys on a numeric key pad rather than the number above the letters?
yes
That's nearly always disabled by default but can be enabled.
from windows system?
Again you are straying away from your original Question :--(
sorry!
thanks for your support & assistance
yes
That's nearly always disabled by default but can be enabled.
from windows system?
Again you are straying away from your original Question :--(
sorry!
thanks for your support & assistance
Abdo M (rep: 26) Jul 20, '26 at 10:03 am
Thanks for selecting my Answer, Abdo.
If the numbers on that pad work, it's enabled. If not, you can enable it temporarily by pressing the Num Lock key. You can also adjust a Registry entry so it is always enabled on startup (search the internet for details).
If the numbers on that pad work, it's enabled. If not, you can enable it temporarily by pressing the Num Lock key. You can also adjust a Registry entry so it is always enabled on startup (search the internet for details).
John_Ru (rep: 6832) Jul 20, '26 at 10:33 am