Hi Community !

I have a huge loop which opens some files from the server of the company I
work.
I have a search button, for pieces about to arrive our warehouse.
I need to know if there's something I can do after all the loop, if any
piece was found, the message "NO RESULTS FOR THE ITEM YOU ARE SEARCHING."
apperas in line 1 of the listbox.

I do not want to change the loop routine, I just need a command after the
loop.

Thank you !

Hello Eddie,

Not sure I really understand. You say "if any piece was found, the message
"NO RESULTS FOR THE ITEM YOU ARE SEARCHING." apperas in line 1 of the listbox.

Am I correct in assuming that you are getting this message when something is
found? If so, can you share the code you have otherwise it is difficult to
know what needs to be altered to correct it.

If my assumption is not correct then perhaps some more explanation as well
as sharing the code.

--
Regards,

OssieMac


"Eddie_SP" wrote:

Hi Community !

I have a huge loop which opens some files from the server of the company I
work.
I have a search button, for pieces about to arrive our warehouse.
I need to know if there's something I can do after all the loop, if any
piece was found, the message "NO RESULTS FOR THE ITEM YOU ARE SEARCHING."
apperas in line 1 of the listbox.

I do not want to change the loop routine, I just need a command after the
loop.

Thank you !

I don't think I'd change the listbox. I'd just use a label (and maybe disable
the listbox???).

But maybe this would give you an idea.

I created a userform with a listbox and a commandbutton. This was the code
under the userform:

Option Explicit
Private Sub CommandButton1_Click()
Dim myArr As Variant
Dim iCtr As Long
With Me.ListBox1
myArr = .List
.Clear
.AddItem "NO RESULTS FOR THE ITEM YOU ARE SEARCHING."
For iCtr = LBound(myArr) To UBound(myArr)
.AddItem myArr(iCtr, 0)
Next iCtr
End With
End Sub
Private Sub UserForm_Initialize()
Dim iCtr As Long
'add some test data
With Me.ListBox1
For iCtr = 1 To 5
.AddItem "AAA" & iCtr
Next iCtr
End With
End Sub




Eddie_SP wrote:

Hi Community !

I have a huge loop which opens some files from the server of the company I
work.
I have a search button, for pieces about to arrive our warehouse.
I need to know if there's something I can do after all the loop, if any
piece was found, the message "NO RESULTS FOR THE ITEM YOU ARE SEARCHING."
apperas in line 1 of the listbox.

I do not want to change the loop routine, I just need a command after the
loop.

Thank you !

--

Dave Peterson

Hi OssieMac and Dave...

Here is the code:

Private Sub CommandButton1_Click()
Dim ComboRef As String
Dim i As Integer
Dim c As Integer
Dim Pedido As String
Dim Aba As String
Dim Item As String

ComboRef = Me.ComboBox1.Value
i = 6

If ComboRef = "" Then
MsgBox ("Selecione o Fornecedor.")
Me.ComboBox1.SetFocus
Exit Sub
End If

If Me.TextBox1.Value = "" Then
MsgBox ("Insira a peça que deseja buscar.")
Me.TextBox1.SetFocus
Exit Sub
End If

Me.ListBox1.Clear

With Me.ListBox1
.ColumnCount = 3
.ColumnWidths = "60;30;210"

Worksheets(4).Activate
While (ActiveSheet.Cells(i, 1) < 0)
i = i + 1
Windows("Matriz.xls").Activate
Worksheets(4).Activate
If Cells(i, 2).Text = ComboRef And Cells(i, 34).Text =
"PENDENTE" Then


Item = UCase(Trim(Me.TextBox1.Text))

Workbooks.Open ("\\brfile100vm\edivando
souza\Importação\Importação - Cads\" & ComboRef & ".xls")
Windows("Matriz.xls").Activate
Aba = Worksheets("Pedidos").Cells(i, 1).Text
Windows(ComboRef & ".xls").Activate
Worksheets(Aba).Activate

c = 1

While (ActiveSheet.Cells(c, 1) < 0)
c = c + 1
If Cells(c, 5).Value = Item Then
.AddItem Aba
.List(.ListCount - 1, 1) = Cells(c, 4).Text
.List(.ListCount - 1, 2) = Cells(c, 8).Text
End If
Wend
End If
Wend

i = 31
c = 1
Windows("Matriz.xls").Activate
Worksheets(5).Activate
While (ActiveSheet.Cells(i, 1) < 0)
i = i + 1
Windows("Matriz.xls").Activate
Worksheets(5).Activate
If Cells(i, 2).Text = ComboRef And Cells(i, 34).Text =
"PENDENTE" Then


Item = UCase(Trim(Me.TextBox1.Text))

Workbooks.Open ("\\brfile100vm\edivando
souza\Importação\Importação - Cads\" & ComboRef & ".xls")
Windows("Matriz.xls").Activate
Aba = Worksheets("MarÃ*timos").Cells(i, 1).Text
Windows(ComboRef & ".xls").Activate
Worksheets(Aba).Activate

c = 1

While (ActiveSheet.Cells(c, 1) < 0)
c = c + 1
If Cells(c, 5).Value = Item Then
.AddItem Aba
.List(.ListCount - 1, 1) = Cells(c, 4).Text
.List(.ListCount - 1, 2) = Cells(c, 8).Text
End If
Wend
End If
Wend
End With

If ActiveWorkbook.Name < ThisWorkbook.Name Then
ActiveWorkbook.Close False
End If

End Sub

This code is used to search for items which are oming in importations.
The user selects the Company (Exporter) and types the item #.
At the end the item is shown on ListBox if it is being imported.
If it is not being imported, any message appears...

Can you help me on that?

Best Regards !!!

I gave the code to OssiecMac...

If you use "Notify me of replies", this is just for you to receive the
message.

Thank you Dave, as always... =)

I don't understand.

Do you want the message if you don't find anything or if you do find something?

You may be able to just look at the .listcount of that listbox:

if me.listbox1.listcount = 0 then
.additem "your string here"
end if



Eddie_SP wrote:

I gave the code to OssiecMac...

If you use "Notify me of replies", this is just for you to receive the
message.

Thank you Dave, as always... =)

--

Dave Peterson

THAT'S IIIIIIT !!! =)

Worked perfectly...

Sorry if I made confusion Dave...

Thank you man !!!

:D

My standard state is confusion, so no need to be sorry <vbg.

Glad you got what you wanted.

I still would think about disabling the listbox and using a label (nice big red
letters!).



Eddie_SP wrote:

THAT'S IIIIIIT !!! =)

Worked perfectly...

Sorry if I made confusion Dave...

Thank you man !!!

:D

--

Dave Peterson

haha...

Dave, using label might be a problem...
Because the Listbox is used for both situations, when it finds the items or
when it doesn't.
But the thing is... Maybe the same item may be included in 4 different
shipments, so it goes adding the lines below...

Man, thank you very much for your kind help...

Great job ! =)