PDA

View Full Version : [SOLVED] Need Error Check



oldman
09-30-2013, 12:39 PM
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
09-30-2013, 12:43 PM
You can populate a combobox with all valid SKU numbers.
If the user selects one a 'not found' message is not necessary.

oldman
09-30-2013, 12:56 PM
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
09-30-2013, 02:10 PM
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
09-30-2013, 02:34 PM
Why should I. It should be blank when I open the userform. My mistake.

Thank you

snb
10-01-2013, 02:50 AM
Every time the userform is loaded the combobox is empty by default.

oldman
10-01-2013, 06:30 AM
Correct

SamT
10-01-2013, 07:51 AM
Thread Solved? See Thread Tools.

oldman
10-01-2013, 08:16 AM
Thank you for the reminder. Just like being at home.

SamT
10-02-2013, 04:27 PM
Us ole pharts gotta help each other keep the olds-himers in check, ya no?

oldman
10-03-2013, 12:35 PM
Too funny