Search box on userform

[ipbr21054]

Morning,

I have a userform with a Combobox which is populated from customers names from my POSTAGE worksheet.
I would like to select a customers name from the list & then be taken to that customer on my worksheet.
Currently my worksheet is sorted by date example A8 01/10/2015 to current info cell A646 30/10/2018
This then means that the customers names in column B are not in any order.

This is how it should work.
Sort ComboBox names A-Z
Browse through the list & select a customer.
Upon selecting a customer close userform
Then that customer is selected on my worksheet.

Here is some information to assist you.

Worksheet is called POSTAGE
Customers name will always be in column B
The range is B8 & onwards
The userform is called PostageTransferSheet
The Combobox is called CustomerSearchBox

Thanks & have a nice day.

 

[My Aswer Is This]

It is not possible to sort a Combobox list
So what we must do is sort the list of names before loading them into the Combobox
So what this script does is copies all the names in column B starting in row(8)

The script pastes the names into Column L starting in Row(1)
Then we sort the list in column L and load's the name into the combobox
Then the script clears the names in column L

If column L is already in use by something else tell me what column I could use

And the script I have for the Combobox does what you wanted.

Post these two scripts into your Userform

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("B2: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

 

[ipbr21054]

Hi,
Column L is fine & not in use but when the names are placed there can we have the first row as 8 NOT 1

When i go to this sheet the userform no loner opens automatically because I get a run time error 70 Permission denied.

When i debug i see this in yellow.

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

ALSO
I have a Private Sub Userform Initalize in use which we need to also included.
"it was removed so i could run your code"

Private Sub UserForm_Initialize()
TextBox1.Value = Now
TextBox1 = Format(TextBox1.Value, "dd/mm/yyyy")
TextBox2.SetFocus
 
End Sub

If i look at Column L i do see all the names there if that helps

 

[My Aswer Is This]

Not sure what your saying.
The script starts looking for names in Column B row 8 like you asked for.
What is your reason for wanting the names in column L starting in row 8 instead of row 1.
These values will be cleared out 1 second later and you will never know they were there.

The only reason you saw the values in column L is because the script had a error for some reason before the script cleared those names out of column L

As far as your other code in the

Initialize code just add yours after the code I provided.

​

 

[My Aswer Is This]

AS far as the error code:

When i debug i see this in yellow.

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

You need to look at that and see what might be wrong. It works for me.
Is the sheet name correct?
Is the

CustomerSearchBox

Name correct?

Are you sure it's named:

CustomerSearchBox

Or is it named

CustomerSearch

​

​

_{^{<strike>
</strike>}}​

​

<strike></strike>

 

[ipbr21054]

Hi,
I have checked your advice in post #5 and i dont see any issues.
reading the code at what point does the userform close & the customer then be selected in my worksheet ?

I still have the same issue,see my screen shots to see if you spot anything.

 

[My Aswer Is This]

I do not know what to say:

The script works for me.

I only suggest you check the:
CustomerSearchBox


And make sure there is not a space after Box

Put this little script into a command Button on your Userform
Then click the button and see what happens.
It should Turn the Combobox Back Color to red.

This will test to see if its name is as you say.
If you get a Error code then the Combobox name is wrong.
Maybe there is a space or two after Box

Private Sub CommandButton3_Click()
CustomerSearchBox.BackColor = vbRed
End Sub

 

[ipbr21054]

OK
I had to remove the code mentioned in post #2 as the error message kept popping up.

There was no space,i even copied your text and pasted it.

When i click on the new button the combobox turns red.

 

[ipbr21054]

In the line of code that is shown in yellow,recap of code shown below.
Can you explain the part which is .Cells(1, 12)

I ask because when i put the mouse over it i see a name.
When i look down column B the name in question is in Row 66 ?

This is why i ask what this line of code is doing.

Just had a quick look at the names in column L and this name is then 1st

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

 

[My Aswer Is This]

Well if you remove that line of code then Your Combobox will not load with the names.

Try removing this line of code and see if it fixes the problem
This will stop clearing the values in column L
If that fixes the problem then let me know and I can fix it later.

Sheets("POSTAGE").Cells(1, 12).Resize(Lastrowa).Clear