Sorting of ComboBox list

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,652
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.
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
997
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
3,652
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
3,652
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
997
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
3,652
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
997
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
3,652
Office Version
  1. 2007
Platform
  1. Windows
Im going mad as i expect you are also.

Yes userform,dont know why i wrote sheet ??
 

Watch MrExcel Video

Forum statistics

Threads
1,109,466
Messages
5,528,968
Members
409,848
Latest member
Blomsten
Top