Need Error Check

[oldman]

I need to fire a message box "SKU Not on File" if the value of tsku is not found in the source worksheet.


Private Sub cmdqf_Click() 'Populates worksheet automatically from listbox
Application.ScreenUpdating = False
Dim ws As Worksheet
Set ws = Worksheets("SKU LineUp")
Dim nullstring
Application.ScreenUpdating = False

If tsku.Value = "" Or nullstring Then
MsgBox "Please Enter SKU Number"

tsku.SetFocus
Exit Sub
End If
ws.AutoFilterMode = False
ws.Range("b1:g1").AutoFilter Field:=1, Criteria1:="*" & tsku.Text & "*"

With ws.AutoFilter.Range.Offset(1, 0)
.Copy Sheets("Pop").Range("b1")

End With

Me.lb1.List = Worksheets("POP").Cells(1, 1).CurrentRegion.Value
Me.lb1.Selected(0) = True

tsku.Value = ""
End Sub

[snb]

You can populate a combobox with all valid SKU numbers.
If the user selects one a 'not found' message is not necessary.

[oldman]

Will this still allow a user to type in the complete SKU number and clear the combobox after the item is found?

Do I simply make an array of the sku numbers for the combobox?

BTW, much of this project is a result of your help

[snb]

Apparently the sku numbers reside in column B.
I assume it contains only unique sku's.
So you can populate the combobox using

Combobox.list=columns(2).specialcells(2).Value

Why would you like to clear the combobox ?

[oldman]

Why should I. It should be blank when I open the userform. My mistake.

Thank you

[snb]

Every time the userform is loaded the combobox is empty by default.

[oldman]

Correct

[SamT]

Thread Solved? See Thread Tools.

[oldman]

Thank you for the reminder. Just like being at home.

[SamT]

Us ole pharts gotta help each other keep the olds-himers in check, ya no?

[oldman]

Too funny