Sorting of ComboBox list

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,254
Office Version
  1. 2007
Platform
  1. Windows
Afternoon.

I have a ComboBox on my userform which when i click the drop down arrow the names then shown are in order of A-Z & taken from my worksheet column B, "if i select a name from that list i am then taken to the person on the worksheet"

Now i would like to use the same code BUT without the part shown within the "" "" above.

This is the code.
Code:
Private Sub CustomerSearchBox_Change()'Modified  10/3/2018  5:51:42 AM  EDT
Dim SearchString As String
Dim SearchRange As Range
SearchString = CustomerSearchBox.Value
Dim LastRow As Long
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
Set SearchRange = Range("B8:B" & LastRow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
SearchRange.Select
Unload Me
End Sub

My second ComboBox is called CustomerSearchBox2
Please can you advise how i should write it as whatever i do shows nothing when i click the drop down arrow.

Thanks.

Basically i am just looking for the list to appear in the order of A-Z & these names are taken from B8 & onwards.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,439
Office Version
  1. 2010
Platform
  1. Windows
whatever i do shows nothing when i click the drop down arrow
Look at the UserForm_Initialize routine to see how you populated the first CustomerSearchBox list.
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,254
Office Version
  1. 2007
Platform
  1. Windows
Hi,
Here are the 2 bits of code that are in use.
I have changed the text to match up with my 2nd combobox

Code:
Private Sub CustomerSearchBox2_Change()'Modified  10/3/2018  5:51:42 AM  EDT
Dim SearchString As String
Dim SearchRange As Range
SearchString = CustomerSearchBox2.Value
Dim LastRow As Long
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
Set SearchRange = Range("B8:B" & LastRow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
SearchRange.Select
Unload Me
End Sub
Private Sub UserForm_Initialize()
'Modified  10/3/2018  5:51:42 AM  EDT
Application.ScreenUpdating = False
Dim LastRow As Long
LastRow = Sheets("POSTAGE").Cells(Rows.Count, "B").End(xlUp).Row
Dim Lastrowa As Long
Sheets("POSTAGE").Cells(8, 2).Resize(LastRow - 7).Copy Sheets("POSTAGE").Cells(1, 12)
Lastrowa = Sheets("POSTAGE").Cells(Rows.Count, "M").End(xlUp).Row
Sheets("POSTAGE").Cells(1, 12).Resize(Lastrowa).Sort key1:=Cells(1, 12).Resize(Lastrowa), order1:=xlAscending, Header:=xlNo
CustomerSearchBox2.List = Sheets("POSTAGE").Cells(1, 12).Resize(Lastrowa).Value
Sheets("POSTAGE").Cells(1, 12).Resize(Lastrowa).Clear
Application.ScreenUpdating = True


End Sub

It looks like it copies the names in column B then sorts them in column M
Now the drop down list should be sorted A-Z

Whilst the 1st combobox works fine this still shows no names.
I have reflected the 2 at the end of the CustomerSearchBox
I see an error message,
Run time error 381
Could not set the list property Invalid property array index
This is strange as the other has the same code & works fine.

This is shown in yellow when i debug
CustomerSearchBox2.List = Sheets("POSTAGE").Cells(1, 12).Resize(Lastrowa).Value

I dont see what the fault is ?
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,254
Office Version
  1. 2007
Platform
  1. Windows
In the Initialize code if i hover over the word CustomerSearchBox2.List
I see CustomerSearchBox2.List = Null

Yet again i dont see what that is.
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,439
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

This is strange as the other has the same code & works fine.
can you define 'other' ?
why have you removed the initialization of the original CustomerSearchBox ?
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,254
Office Version
  1. 2007
Platform
  1. Windows
On the sheet i have 2 comboboxes
This is the original with its code.

Code:
Private Sub CustomerSearchBox_Change()'Modified  10/3/2018  5:51:42 AM  EDT
Dim SearchString As String
Dim SearchRange As Range
SearchString = CustomerSearchBox.Value
Dim LastRow As Long
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
Set SearchRange = Range("B8:B" & LastRow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
SearchRange.Select
Unload Me
End Sub
Private Sub UserForm_Initialize()
'Modified  10/3/2018  5:51:42 AM  EDT
Application.ScreenUpdating = False
Dim LastRow As Long
LastRow = Sheets("POSTAGE").Cells(Rows.Count, "B").End(xlUp).Row
Dim Lastrowa As Long
Sheets("POSTAGE").Cells(8, 2).Resize(LastRow - 7).Copy Sheets("POSTAGE").Cells(1, 12)
Lastrowa = Sheets("POSTAGE").Cells(Rows.Count, "L").End(xlUp).Row
Sheets("POSTAGE").Cells(1, 12).Resize(Lastrowa).Sort key1:=Cells(1, 12).Resize(Lastrowa), order1:=xlAscending, Header:=xlNo
CustomerSearchBox.List = Sheets("POSTAGE").Cells(1, 12).Resize(Lastrowa).Value
Sheets("POSTAGE").Cells(1, 12).Resize(Lastrowa).Clear
Application.ScreenUpdating = True
End Sub

This is the additional that ive added & need to correctly sort the code out.

Code:
Private Sub CustomerSearchBox2_Change()'Modified  10/3/2018  5:51:42 AM  EDT
Dim SearchString As String
Dim SearchRange As Range
SearchString = CustomerSearchBox2.Value
Dim LastRow As Long
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
Set SearchRange = Range("B8:B" & LastRow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
SearchRange.Select
Unload Me
End Sub
Private Sub UserForm_Initialize2()
'Modified  10/3/2018  5:51:42 AM  EDT
Application.ScreenUpdating = False
Dim LastRow As Long
LastRow = Sheets("POSTAGE").Cells(Rows.Count, "B").End(xlUp).Row
Dim Lastrowa As Long
Sheets("POSTAGE").Cells(8, 2).Resize(LastRow - 7).Copy Sheets("POSTAGE").Cells(1, 12)
Lastrowa = Sheets("POSTAGE").Cells(Rows.Count, "M").End(xlUp).Row
Sheets("POSTAGE").Cells(1, 12).Resize(Lastrowa).Sort key1:=Cells(1, 12).Resize(Lastrowa), order1:=xlAscending, Header:=xlNo
CustomerSearchBox2.List = Sheets("POSTAGE").Cells(1, 12).Resize(Lastrowa).Value
Sheets("POSTAGE").Cells(1, 12).Resize(Lastrowa).Clear
Application.ScreenUpdating = True


End Sub

I define OTHER as the original combobox

I havent removed the initialization of the original.
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,439
Office Version
  1. 2010
Platform
  1. Windows
On the sheet i have 2 comboboxes
thought this was on a user form

You can only have one UserForm_Initialize per user form.

simply add this line immediately below populating the list of the original combo box in the original UserForm_Initialize
Code:
CustomerSearchBox2.List = Sheets("POSTAGE").Cells(1, 12).Resize(Lastrowa).Value
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,254
Office Version
  1. 2007
Platform
  1. Windows
Im going mad as i expect you are also.

Yes userform,dont know why i wrote sheet ??
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,652
Messages
5,838,603
Members
430,557
Latest member
MK15

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