Sorting of ComboBox list

[ipbr21054]

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.

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.

 

[NoSparks]

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]

Hi,
Here are the 2 bits of code that are in use.
I have changed the text to match up with my 2nd combobox

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]

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]

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]

On the sheet i have 2 comboboxes
This is the original with its 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.

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]

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

CustomerSearchBox2.List = Sheets("POSTAGE").Cells(1, 12).Resize(Lastrowa).Value

 

[ipbr21054]

Im going mad as i expect you are also.

Yes userform,dont know why i wrote sheet ??